macro separates .csv by comma, despite separator set to semicolon

0
votes

Within the code attached below, macro opens .csv with values separated by semicolon and comma (at least it looks like it's doing so), despite comma speparator set to false and semicolon separator set to true.

I tried to switch the order of pieces of the code, but to no avail. The history and the purpose of the code can be found in this thread: VBA run-time error '1004' while looping through .csv file. Please advise what I am doing wrong or missing here?

(Topic associated with this question.)

Dim wrk As Workbook
Dim Sh As Worksheet
Dim findMatch As Range, searchInColumn As Range
Dim i As Long, j As Long, k As Long
Dim chosenFile As Integer
Dim xlFileName As String
Dim chooseFiles As Office.FileDialog

Set DictChanged = CreateObject("Scripting.Dictionary")
Set DictToBeDone = CreateObject("Scripting.Dictionary")

Application.ScreenUpdating = False


Set chooseFiles = Application.FileDialog(msoFileDialogFilePicker)

    With chooseFiles

        .AllowMultiSelect = True
        .Title = "Please select the file."
        .InitialFileName = "c:\"
        .InitialView = msoFileDialogViewList
        .Filters.Add "All", "*.*"

    End With


If chooseFiles.Show = -1 Then
    For k = 1 To chooseFiles.SelectedItems.Count
        xlFileName = chooseFiles.SelectedItems(k)

        Set wrk = Application.Workbooks.Open(xlFileName)
        Set Sh = wrk.Worksheets(1)               

        If InStr(1, wrk.Name, ".csv") Then

            Sh.Range(Range("A1"), Range("A1").End(xlDown)).TextToColumns _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=True, Comma:=False, Space:=False, Other:=False

        End If
End If

The described problems occur within the visible code. Below output stages as requested:

Output stages:

Initial File

+---------+---------+---------+------------------+------------+-----------------+ | column1 | column2 | column3 | column4 | column5 | column6 | +---------+---------+---------+------------------+------------+-----------------+ | 123456 | 654321 | SomeID | ValuesWithComma | SomeValues | SomeOtherValues | +---------+---------+---------+------------------+------------+-----------------+ | 123456 | 654322 | SomeID | Values,WithComma | SomeValues | SomeOtherValues | +---------+---------+---------+------------------+------------+-----------------+ | 123456 | 654323 | SomeID | Values,WithComma | SomeValues | SomeOtherValues | +---------+---------+---------+------------------+------------+-----------------+ | 123456 | 654324 | SomeID | Val,uesWithComma | SomeValues | SomeOtherValues | +---------+---------+---------+------------------+------------+-----------------+ | 123456 | 654325 | SomeID | ValuesWithComma | SomeValues | SomeOtherValues | +---------+---------+---------+------------------+------------+-----------------+ | 123456 | 654326 | SomeID | ValuesWithComma | SomeValues | SomeOtherValues | +---------+---------+---------+------------------+------------+-----------------+ | 123456 | 654327 | SomeID | ValuesWithComma | SomeValues | SomeOtherValues | +---------+---------+---------+------------------+------------+-----------------+ | 123456 | 654328 | SomeID | ValuesWithComma | SomeValues | SomeOtherValues |

2.AfterPickingFileInFileDialog

+-----------------------------------------------------------------+-----------------------------------------+ | column1;column2;column3;column4;column5;column6 | column2 | +-----------------------------------------------------------------+-----------------------------------------+ | 123456;654321;SomeID;ValuesWithComma;SomeValues;SomeOtherValues | 654321 | +-----------------------------------------------------------------+-----------------------------------------+ | 123456;654322;SomeID;Values | WithComma;SomeValues;SomeOtherValues | +-----------------------------------------------------------------+-----------------------------------------+ | 123456;654323;SomeID;Values | WithComma;SomeValues;SomeOtherValues | +-----------------------------------------------------------------+-----------------------------------------+ | 123456;654324;SomeID;Val | uesWithComma;SomeValues;SomeOtherValues | +-----------------------------------------------------------------+-----------------------------------------+ | 123456;654325;SomeID;ValuesWithComma;SomeValues;SomeOtherValues | 654325 | +-----------------------------------------------------------------+-----------------------------------------+ | 123456;654326;SomeID;ValuesWithComma;SomeValues;SomeOtherValues | 654326 | +-----------------------------------------------------------------+-----------------------------------------+ | 123456;654327;SomeID;ValuesWithComma;SomeValues;SomeOtherValues | 654327 | +-----------------------------------------------------------------+-----------------------------------------+ | 123456;654328;SomeID;ValuesWithComma;SomeValues;SomeOtherValues | 654328 | +-----------------------------------------------------------------+-----------------------------------------+

3.AfterLastCondition

+---------+---------+---------+-----------------+------------+-----------------+ | column1 | column2 | column3 | column4 | column5 | column6 | +---------+---------+---------+-----------------+------------+-----------------+ | 123456 | 654321 | SomeID | ValuesWithComma | SomeValues | SomeOtherValues | +---------+---------+---------+-----------------+------------+-----------------+ | 123456 | 654322 | SomeID | Values | | | +---------+---------+---------+-----------------+------------+-----------------+ | 123456 | 654323 | SomeID | Values | | | +---------+---------+---------+-----------------+------------+-----------------+ | 123456 | 654324 | SomeID | Val | | | +---------+---------+---------+-----------------+------------+-----------------+ | 123456 | 654325 | SomeID | ValuesWithComma | SomeValues | SomeOtherValues | +---------+---------+---------+-----------------+------------+-----------------+ | 123456 | 654326 | SomeID | ValuesWithComma | SomeValues | SomeOtherValues | +---------+---------+---------+-----------------+------------+-----------------+ | 123456 | 654327 | SomeID | ValuesWithComma | SomeValues | SomeOtherValues | +---------+---------+---------+-----------------+------------+-----------------+ | 123456 | 654328 | SomeID | ValuesWithComma | SomeValues | SomeOtherValues | +---------+---------+---------+-----------------+------------+-----------------+

2
excelvba
"at least it looks like it's doing so" It seems like there's a bit more investigation required before we can actually confirm there is a problem here. The posted code is incomplete and calls TextToColumns twice. My understanding is that you first open the file and it all appears in one column, then you call texttocolumns on that. You need to inspect the output at each stage and understand what is going on – Nick.McDermaid
Looks like you're opening the same file 3 times? – Tim Williams
In general, you are better off importing a .csv file than opening it. If you must open it, and it has non-standard delimiters (or date format that differs from your windows regional settings, then you can change the file type to .txt. – Ron Rosenfeld
See here for an example of ‘Open’ with a delimiter stackoverflow.com/questions/17832618/… apparently you need to ensure the extension is .txt though. – Nick.McDermaid
The "Initial file" has no semicolons. And your screenshot of data is virtually useless for doing proper troubleshooting. It cannot be copy/pasted into a worksheet. One can try an OCR program, or manually enter it. Having to do either of these is discouraging to those who might assist you. To make the data useful edit your question to post it as text, perhaps using this Markdown Tables Generator, or possibly upload the *.csv file (with sensitive information removed) to some public website and post a link in your original question – Ron Rosenfeld

2 Answers

1
votes

There are issues with your code as commented by @Tim Williams and @Nick.McDermaid. It is not clear what you want to achieve. However regarding opening Semicolon Delimited text file you may rename the file to .txt and open like 

xlFileName = chooseFiles.SelectedItems(k)
        TmpFlName = Path & "TmpCsv.txt"
        If Dir(TmpFlName) <> "" Then Kill TmpFlName
        FileCopy xlFileName, TmpFlName
        Workbooks.OpenText Filename:=TmpFlName, Origin:= _
        1250, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, TrailingMinusNumbers:=True, Local:=False

issue was delimiter option will only work when .txt file is used not .csv.

0
votes

Thanks to @Ahmed AU answer and comments above I edited my code so it seems to work now. It is supposed to work with .csv and .xlmx files. Here is a bit longer version of the code:

Sub FixCSV()

Dim wrk As Workbook
Dim Sh As Worksheet
Dim findMatch As Range, searchInColumn As Range
Dim i As Long, j As Long, k As Long, lastRow As Long, lastColumn As Long
Dim chosenFile As Integer
Dim xlFileName As String
Dim chooseFiles As Office.FileDialog

Set chooseFiles = Application.FileDialog(msoFileDialogFilePicker)

    With chooseFiles       
        .AllowMultiSelect = True
        .Title = "Please select the file."
        .InitialFileName = "c:\"
        .InitialView = msoFileDialogViewList
        .Filters.Add "All", "*.*"            
    End With           

If chooseFiles.Show = -1 Then
    For k = 1 To chooseFiles.SelectedItems.Count
        xlFileName = chooseFiles.SelectedItems(k)

            If InStr(1, xlFileName, ".csv") Then
                TmpFlName = path & "TmpCsv.txt"
                If Dir(TmpFlName) <> "" Then Kill TmpFlName
                FileCopy xlFileName, TmpFlName
                Workbooks.OpenText FileName:=TmpFlName, origin:= _
                1250, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
                , Space:=False, Other:=False, TrailingMinusNumbers:=True, Local:=False

                Set wrk = Application.Workbooks("TmpCsv.txt")
                Set Sh = wrk.Worksheets(1)
            Else
                Set wrk = Application.Workbooks.Open(xlFileName)
                Set Sh = wrk.Worksheets(1)
            End If

        lastRow = Sh.Cells(Sh.Rows.Count, "A").End(xlUp).Row
        lastColumn = Sh.Cells(1, Sh.Columns.Count).End(xlToLeft).Column           

       i = 2           
       Do Until i = lastRow                          
          'do something          
       Loop

    If InStr(1, wrk.Name, "TmpCsv.txt") Then             
        wrk.SaveAs FileName:=xlFileName, FileFormat:=xlCSV, Local:=True
        wrk.Close False
        Kill TmpFlName
    Else
        wrk.Close 'savechanges:=true
    End If

    Next k
End If

End Sub

While saving as .csv, commas popped up as separators, destroying the file again - this answer by user2726096: https://stackoverflow.com/a/18492514/10348607 helped me to solve the issue.