VBA - CSV File after Import

[Dajnoxes]

Hi,

I have issue with CSV file, I don't know how to exactly explain it but I will try my best

I'm generating CSV file in different tool and it looks like below:

word1,word2,word3,word4,word5,word6
word1,word2,word3,word4,word5,word6
word1,word2,word3,word4,word5,word6
word1,word2,word3,word4,word5,word6

and after that I'm using macro in different excel sheet:

Sub Makro2()
Dim fd1 As Office.FileDialog
    Set fd1 = Application.FileDialog(msoFileDialogFilePicker)
    With fd1
            .Filters.Clear
            .Title = "Select a CSV File"
            .Filters.Add "CSV", "*.csv"
            .AllowMultiSelect = False
        Dim sFile1 As String
        If .Show = True Then
            sFile1 = .SelectedItems(1)
        End If
    End With

If sFile1 <> "" Then
    Open sFile1 For Input As #1
    row_number1 = 1
    Do Until EOF(1)
    Line Input #1, LineFromFile1
    LineItems1 = Split(LineFromFile1, ",")
    For I = 0 To UBound(LineItems1) Step 1:
         Range("A1:F1").Cells(row_number1, I + 1).Value = LineItems1(I)
    Next I
        row_number1 = row_number1 + 1
    Loop
Close #1
End If
End Sub

and now is the moment where I have a issue... when I will open CSV file and I will save it(ctrl+s) and close the file
my macro will work and I will get the results:

word1	word2	word3	word4	word5	word6
word1	word2	word3	word4	word5	word6
word1	word2	word3	word4	word5	word6
word1	word2	word3	word4	word5	word6

but when I will not open CSV file that has been generated by tool, I will get results:

word1

word2

word3

word4

word5

word6

word1

word2

word3

word4

word5

word6

word1

word2

word3

word4

word5

word6

word1

word2

word3

word4

word5

word6

It sees it as 1 line but truly in excel sheet

it's all in different rows.

Can I somehow fix it, I know that it's not a big deal but I need sometimes to open 200 csv files

Thank you,

 

[rlv01]

What is your list separator character?

Sub GetListSep()
    Dim LS As String
    
    LS = CStr(Application.International(xlListSeparator))    ' List Separator
    
    Debug.Print "ANSI Code Page setting on this PC is " & CreateObject("WScript.Shell").RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage\ACP")
    Debug.Print "The as-found list separator is " & "[" & LS & "](Ascii " & Asc(LS) & ")" & vbCr & vbCr
End Sub

 

[Dajnoxes]

What is your list separator character?

Sub GetListSep()
    Dim LS As String
   
    LS = CStr(Application.International(xlListSeparator))    ' List Separator
   
    Debug.Print "ANSI Code Page setting on this PC is " & CreateObject("WScript.Shell").RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage\ACP")
    Debug.Print "The as-found list separator is " & "[" & LS & "](Ascii " & Asc(LS) & ")" & vbCr & vbCr
End Sub

ANSI Code Page setting on this PC od 1250
The as-found list separator is [ ; ] (Ascii 59)

I disabled my split and it all occurs in A1 word1,word2,word3,word4,word5,word6
word1,word2,word3,word4,word5,word6
word1,word2,word3,word4,word5,word6

When I will open file and do a save then it's
A1, A2, A3

 

[rlv01]

Since your list separator is a semi-colon, I'm pretty sure that when you save a worksheet like this:

Book1
	A	B	C	D	E	F
1	word1	word2	word3	word4	word5	word6
2	word1	word2	word3	word4	word5	word6
3	word1	word2	word3	word4	word5	word6
4	word1	word2	word3	word4	word5	word6
Sheet3

To a 'CSV' file, then open it in notepad, it will look like this:

word1;word2;word3;word4;word5;word6 word1;word2;word3;word4;word5;word6 word1;word2;word3;word4;word5;word6 word1;word2;word3;word4;word5;word6

because your list separator char is a semicolon.

If you want to save to a comma separated file, you will need to write code to do that.

 

[Dajnoxes]

Since your list separator is a semi-colon, I'm pretty sure that when you save a worksheet like this:

Book1
	A	B	C	D	E	F
1	word1	word2	word3	word4	word5	word6
2	word1	word2	word3	word4	word5	word6
3	word1	word2	word3	word4	word5	word6
4	word1	word2	word3	word4	word5	word6
Sheet3

To a 'CSV' file, then open it in notepad, it will look like this:

word1;word2;word3;word4;word5;word6 word1;word2;word3;word4;word5;word6 word1;word2;word3;word4;word5;word6 word1;word2;word3;word4;word5;word6

because your list separator char is a semicolon.

If you want to save to a comma separated file, you will need to write code to do that.

Hi,
It's true when I created csv file and then I checked it in notepad I had separator char semicolon.
When I'm checking csv file created by tool, there is comma

I opened file in notepad and there are differences in quotes, it has a impact that everything is showing in 1 cell?

- before save

"word1","word2","word3","word4","word5","word6"
"word1","word2","word3","word4","word5","word6"

- after save

"word1,""word2"",""word3"",""word4"",""word5"",""word6"""
"word1,""word2"",""word3"",""word4"",""word5"",""word6"""

 

[rlv01]

Unless you can figure out a way to post some actual data as produced by "the tool" , not just "word1","word2","word3","word4","word5","word6", I don't think there is any point in continuing.
(Keep in mind when you refer to "the tool" or "different tool" we have no idea what you are talking about).