@Rocky247 Is that file really how it comes out of the POS? When I open it in a text editor it looks like this:
The barcodes are, thus, already included as if they are scientific numbers.
If this is NOT the file from the POS, please upload one of those, without opening and resaving it in Excel first. Use a text editor to remove confidential information and perhaps to reduce the file size.
Hi @Rocky247
> it shows correctly in the system but when exported (as a csv - thats the only option) it is a scientific number as per your screenshot
This sounds like DEAR Systems code generates the output like it was created by Excel instead of writing the raw data directly to a csv file.
Ev figures omitted in rounding to scientific before reaching the csv file are lost while trailing zeros may be rescued.
__/ reading macro
I added another test line with a leading zero
and can via a macro read them as text
Row 26. An inserted leading apostrophe (') makes the cells content commented and not interpreted as a number.
Row 24. Converts scientific format to plain numeric (0). Here, trailing zeros are appended.
Sub CSV_Read()
path = ThisWorkbook.path & "\" & Range("source").Value
foundFile = Dir(path)
If foundFile <> "" Then
Range("target").CurrentRegion.ClearContents
Close
fileNo = FreeFile
Open ThisWorkbook.path & "\" & foundFile For Input As #fileNo
text = Input$(LOF(1), 1)
Close #fileNo
allRowsInArray = Split(text, vbCrLf)
rowCount = UBound(allRowsInArray)
rowCounter = 0
Do While rowCounter < rowCount
currentLineToArray = Split(allRowsInArray(rowCounter), ",")
columnCount = UBound(currentLineToArray)
For columnCounter = 0 To columnCount
If columnCounter = Range("special_columnNo").Value Then
If InStr(currentLineToArray(columnCounter), "E") > 0 Then
Range("target").Offset(rowCounter, columnCounter).Value = "'" & Format(currentLineToArray(columnCounter), "0")
Else
Range("target").Offset(rowCounter, columnCounter).Value = "'" & currentLineToArray(columnCounter)
End If
Else
Range("target").Offset(rowCounter, columnCounter).Value = currentLineToArray(columnCounter)
End If
Next
rowCounter = rowCounter + 1
Loop
End If
End Sub
__/ writing macro
Using macro also for saving the edited csv file, the codes still looks like 'text' and the apostrophe is omitted. Leading zero still there.
Sub CSV_Write()
text = ""
rowCount = Range("target").CurrentRegion.Rows.Count
columnCount = Range("target").CurrentRegion.Columns.Count
For rowCounter = 0 To rowCount - 1
For columnCounter = 0 To columnCount - 1
If columnCounter = 0 Then
text = text & Range("target").Offset(rowCounter, columnCounter)
ElseIf columnCounter = columnCount - 1 Then
text = text & "," & Range("target").Offset(rowCounter, columnCounter) & vbCrLf
Else
text = text & "," & Range("target").Offset(rowCounter, columnCounter)
End If
Next
Next rowCounter
path = Left(Range("source").Value, Len(Range("source").Value) - 4) & "_" & Format(Now(), "YYYY-MM-DD_hhmmss") & Right(Range("source").Value, 4)
path = ThisWorkbook.path & "\" & Replace(path, "*", "")
fileNo = FreeFile
Open path For Output As #fileNo
'Write the selected data into the text file.
Print #fileNo, text 'Write includes "
Close #fileNo
End Sub
__/ prefs
To avoid editing in the macro, you can enter the input files name in P1. The file is expected to be in the same file as the macro.
P2 gives you the possibility to change column, should the output change column with the bar codes.
Q2 counts the columns offset from first column.
The macro outputs the file starting in the cell named target. In the attached file, A5.
__/ Scientific formatting
When Number Format is "General", the column width affects until 12 figures.
Scientific applies rounding and cuts accuracy.
@Rocky247 Since conversion to scientific data may truncate accuracy already in the systems output, you will depend on having a correct combination of product code and bar code outside the host system.
Merging with a previous list where you already have fixed the bar codes manually, you will in the future only have to fix the new products;
Column O may miss accuracy for the last digits or be in scientific format if using Data: From Text/CSV as suggested by @Riny_van_Eekelen.
Column P is looking up the previous bar code for known products.
=LET(
prodCode; OFFSET(A6;0;0;COUNTA(A:A)-1;1);
barCode; OFFSET(prodCode;0;COLUMNS(A:O)-1);
prevOutput;XLOOKUP(prodCode;prevReturn2dear!A:A;prevReturn2dear!O:O);
output; IFS(
ISNA(prevOutput);barCode;
prevOutput=barCode;prevOutput;
1;prevOutput
);
comment; IFS(
ISNA(prevOutput);"new product";
prevOutput=barCode;"-";
1;"using previous bar code"
);
output
)Attached also as an xlsX without macros that should be okay if you don't get problems (like conversions to scientific) when saving as csv.