VBA - CSV File after Import

Dajnoxes

New Member
Joined
Aug 7, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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:



word1word2word3word4word5word6
word1word2word3word4word5word6
word1word2word3word4word5word6
word1word2word3word4word5word6

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

word1word2word3word4word5word6word1word2word3word4word5word6word1word2word3word4word5word6word1word2word3word4word5word6

It sees it as 1 line but truly in excel sheet

1659974467617.png


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,
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
What is your list separator character?

VBA Code:
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
 
Upvote 0
What is your list separator character?

VBA Code:
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
 
Upvote 0
Since your list separator is a semi-colon, I'm pretty sure that when you save a worksheet like this:

Book1
ABCDEF
1word1word2word3word4word5word6
2word1word2word3word4word5word6
3word1word2word3word4word5word6
4word1word2word3word4word5word6
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.
 
Upvote 0
Since your list separator is a semi-colon, I'm pretty sure that when you save a worksheet like this:

Book1
ABCDEF
1word1word2word3word4word5word6
2word1word2word3word4word5word6
3word1word2word3word4word5word6
4word1word2word3word4word5word6
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"""
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top