Macro to open CSV file ";" delimited

[cidfidou]

Hi Excel Gods,

I am looking to tweak below VBA code to be able to open X number of CSV file (; delimited) and to have the CSV file in the excel workbook as text (All columns).

The current issue is that all the information per sheet are whitin one col

thanks in advance

Sub test()
Dim myDir As String, fn As String, wb As Workbook
Set wb = ActiveWorkbook
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then myDir = .SelectedItems(1) & ""
End With
If myDir = "" Then Exit Sub
fn = Dir(myDir & "*.csv")
Do While fn <> ""
With Workbooks.Open(myDir & fn)
.Sheets(1).Copy after:=wb.Sheets(wb.Sheets.Count)
.Close False
End With
fn = Dir
Loop
End Sub

 

[bobsan42]

try to replace the ; in the csv file with an actual comma , (comma separated values).

 

[cidfidou]

Hi bob,

Unfortunately i can not do that as this will wreck the file.. I have comma in some of the description

 

[bobsan42]

try this method (after applying the necessary modifications):
This will insert the file contents in the active sheet.

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;[COLOR=#ff0000][B]C:\...\2016-09-29 - Copy.csv[/B][/COLOR]", _
        Destination:=Range("[COLOR=#ff0000][B]$A$1[/B][/COLOR]"))
        '.CommandType = 0
        .Name = "[COLOR=#ff0000][B]2016-09-29 - Copy[/B][/COLOR]" 'The name of the range containing the data
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 866
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
[COLOR=#0000ff]        .TextFileSemicolonDelimiter = True[/COLOR]
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array([COLOR=#ff0000][B]2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2[/B][/COLOR])
            ' 2 means text; the size of the array will depend on the number of columns in your file
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

adjust the bold red parts to suit your needs.

Another option (not tested) is to setup system or excel (if possible) list delimiter to ;

 

[cidfidou]

Hi bob,

thanks for the quick answer. So i take that this is not possible to add a vba code that will "text to column" every CSV file with a dynamic range depending of column number

 

[cidfidou]

I have been looking online for while now and I have found plenty of code to copy multiple CSV files into one workbook but none of them are doing the text to column... i am really surprised !!!

 

[bobsan42]

OK, it took me a while to figure the problem: drop the csv extension - make the files TXT.
your code should be able to do it with a small modification.

Sub test()
    Dim myDir As String, fn As String, wb As Workbook
    Set wb = ActiveWorkbook
    With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show Then myDir = .SelectedItems(1) & ""
    End With
    If myDir = "" Then Exit Sub
    fn = Dir(myDir & "[B][COLOR=#ff0000]\[/COLOR][/B]*.TXT")
    Do While fn <> ""
    With Workbooks.Open(myDir & "[B][COLOR=#ff0000]\[/COLOR][/B]" & fn[B][COLOR=#0000ff], , , 4[/COLOR][/B])
    .Sheets(1).Copy after:=wb.Sheets(wb.Sheets.Count)
    .Close False
    End With
    fn = Dir
    Loop
End Sub

Like I said - change the extensions to TXT for this to work. Then in the open method specify format 4 (the blue part above)
For some reason I had to add a backslash at the end of myDir - check if you need it or remove it if you get an error.
more info on the OPEN method here: https://msdn.microsoft.com/en-us/library/office/ff194819(v=office.15).aspx

 

[cidfidou]

Hi Bob,

thank you so much for the time you have already spent looking at my issue but for some reason when I use the above code nothing is happeming once I have slected the relevant folder with the CSV files. Is there anything I need to modify?
Thanks again

 

[bobsan42]

well - i mentioned it:
Change the file extensions from CSV to TXT.
Then use the code I sent you (and maybe remove the backslashes in red if you get errors)

When the file is CSV the open Method ignores the specified delimiter format:

With Workbooks.Open(myDir & "[B][COLOR=#ff0000]\[/COLOR][/B]" & fn[B][COLOR=#0000ff], , , 4[/COLOR][/B])

So - to specify semicolon as a delimiter - the files have to be TXT.
My code is looking for TXT files. If you have not changed the extensions - it will not find any.

 

[cidfidou]

Wouah!!! Amazing solution!!! thank you so much and sorry for not understanding the first time. You really made my day.
To make it fit for purpose is there a way now to make sure that all the cells will be saved as text (like 2,2,2, you mentioned above)