UTF-8 CSV file's columns not being recognised

[vegasbaby207]

Hi,

I have a relatively simply bit of code that writes 2 columns of data into a text file for importing into another system (NetSuite).
The first column is the item name, a 10 character string.
The second column is the "web copy" - the description the product that eventually flows through to our B2C website. This field has all sorts of characters, meaning that I need the file created and saved as UTF-8 format.

I am using the ADODB.Stream object and the following commands

To write the Header:

fsT.WriteText ActiveCell.Value & ", " & ActiveCell.Offset(0, 160) & vbCrLf

And then I look through all subsequent non-blank rows to write the lines:

fsT.WriteText ActiveCell.Value & ", " & Chr(34) & ActiveCell.Offset(0, 160) & Chr(34) & vbCrLf

The file saves correctly, and to the naked eye looks perfect.
However it does not import into NetSuite, reporting an error of a mismatch of columns.
I investigated further by trying to open the file in Excel. Bizarrely (to me), it does not recognise the double-quotations and any commas contained within the second column are separating into different excel columns.

I'm happy to upload a copy of the CSV file if that would be beneficial to anyone.

Below is a full copy of the VBA sub.

Private Sub btnSaveWeb_Click()

Dim fsT As Object
Dim sFileName As String

sFileName = "G:\TEMP\ItemUploads\Web Copy - " & Format(Date, "yyyy-mm-dd") & ".csv"

Set fsT = CreateObject("ADODB.Stream")
fsT.Type = 2 
fsT.Charset = "utf-8" 
fsT.Open 

Range("A1").Activate
fsT.WriteText ActiveCell.Value & ", " & ActiveCell.Offset(0, 160) & vbCrLf
ActiveCell.Offset(1, 0).Activate

While ActiveCell.Value <> ""
    fsT.WriteText ActiveCell.Value & ", " & Chr(34) & ActiveCell.Offset(0, 160) & Chr(34) & vbCrLf
    ActiveCell.Offset(1, 0).Activate
Wend

fsT.SaveToFile sFileName, 2
 
    
End Sub

 

[rlv01]

Maybe post the first 10 rows of the 2 column data using XL2BB

Book3
	A																																																																																																																																																																FE
1	dimmock																																																																																																																																																																resaluted
2	idrosis																																																																																																																																																																diesel
3	undark																																																																																																																																																																bellwind
4	semitism																																																																																																																																																																unmummify
5	adeem																																																																																																																																																																townees
6	lucanid																																																																																																																																																																quota
7	anoas																																																																																																																																																																miriamne
8	rosarian																																																																																																																																																																eiry
9	birrus																																																																																																																																																																encheiria
10	tuggery																																																																																																																																																																carcanets
Sheet2

along with the first 10 lines of the .csv file

dimmock,resaluted​

idrosis,diesel​

undark,bellwind​

semitism,unmummify​

adeem,townees​

lucanid,quota​

anoas,miriamne​

rosarian,eiry​

birrus,encheiria​

tuggery,carcanets​

 

[Domenic]

I'm not familiar with NetSuite, but try removing the space after your comma delimiter...

fsT.WriteText ActiveCell.Value & "," & ActiveCell.Offset(0, 160) & vbCrLf

fsT.WriteText ActiveCell.Value & "," & Chr(34) & ActiveCell.Offset(0, 160) & Chr(34) & vbCrLf

Hope this helps!

 

[vegasbaby207]

Wow. That did it!!
Amazing.

For reference, here's an example sheet:

Sample.xlsb
	A	B
1	Colour	Description
2	Red	A primary colour, associated with danger. Also used to indicate STOP!
3	Yellow	Another primary colour, bright and cheery. You may recognise it in the SMILEY® icon
4	Blue	This is a simple one. Vivid, bright, friendly, and happy
Sheet1

When you run the original code on the sheet (you'll need to amend the offset from 160 to 1), the text file looks fine, but when you reopen it in excel, it looks like this:

Web Copy - 2022-08-03.csv
	A	B	C	D	E
1	Colour	Description
2	Red	"A primary colour	associated with danger. Also used to indicate STOP!"
3	Yellow	"Another primary colour	bright and cheery. You may recognise it in the SMILEY® icon"
4	Blue	"This is a simple one. Vivid	bright	friendly	and happy"
Web Copy - 2022-08-03

As soon as the space after the delimiter is removed, it works!

Thanks Dominic

 

[rlv01]

You might also consider testing your data for the presence of a comma and only wrapping the data string in double quotes if any are found

Private Sub btnSaveWeb_Click()

Dim fsT As Object
Dim sFileName As String
Dim S1 As String, S2 As String

sFileName = "G:\TEMP\ItemUploads\Web Copy - " & Format(Date, "yyyy-mm-dd") & ".csv"

Set fsT = CreateObject("ADODB.Stream")
fsT.Type = 2
fsT.Charset = "utf-8"
fsT.Open

Range("A1").Activate

While ActiveCell.Value <> ""
    S1 = ActiveCell.Value
    S2 = ActiveCell.Offset(0, 160).Value
    
    If InStr(S1, ",") > 0 Then
        S1 = Chr(34) & S1 & Chr(34)
    End If
    
    If InStr(S2, ",") > 0 Then
        S2 = Chr(34) & S2 & Chr(34)
    End If
    
    fsT.WriteText S1 & "," & S2 & vbCrLf
    ActiveCell.Offset(1, 0).Activate
Wend

fsT.SaveToFile sFileName, 2
End Sub