0

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 | +---------+---------+---------+-----------------+------------+-----------------+

Drzemlik
  • 59
  • 1
  • 9
  • Can you show us a sample of the initial data and the output? – Ricardo Diaz Feb 02 '19 at 00:05
  • 1
    "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.Mc Feb 02 '19 at 00:31
  • 2
    Looks like you're opening the same file 3 times? – Tim Williams Feb 02 '19 at 00:58
  • 2
    In general, you are better off **import**ing a `.csv` file than **open**ing 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 Feb 02 '19 at 01:50
  • @Ricardo Diaz - I have added links. – Drzemlik Feb 02 '19 at 10:11
  • @Nick.McDermaid - "The posted code is incomplete and calls TextToColumns twice... " - Apologies, I've just corrected the code. I did not include the rest of the code as all the described problems occurred withing what's visible. The code worked on several files, until I tried it with .csv with commas in cells(not as default separators). Now I added output stages, it separates by commas on opening the files. I've checked Win settings in Control Panel in Language and Regional, in the list of separators and it's set to semicolon. – Drzemlik Feb 02 '19 at 10:31
  • So at the second stage when you open the file with ‘Workbooks.Open’ it has already split by commas – Nick.Mc Feb 02 '19 at 10:50
  • 1
    See here for an example of ‘Open’ with a delimiter https://stackoverflow.com/questions/17832618/open-csv-file-delimited-by-pipe-character-or-not-common-delimiter apparently you need to ensure the extension is .txt though. – Nick.Mc Feb 02 '19 at 10:53
  • 1
    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](https://www.tablesgenerator.com/markdown_tables), or possibly upload the `*.csv` file (with sensitive information removed) to some public website and post a link in your original question – Ron Rosenfeld Feb 02 '19 at 12:44
  • @Nick.McDermaid - yes, file is already split by commas on opening, now I see that setting TextToColumns Commas:=False couldn't work. Thank you for advice, I edited as instructed, the tables doesn't look good to me now, but they are editable. – Drzemlik Feb 02 '19 at 15:52
  • Possible duplicate of [Import semicolon separated CSV file using VBA](https://stackoverflow.com/questions/39890471/import-semicolon-separated-csv-file-using-vba) – Luuklag Feb 28 '19 at 08:43

2 Answers2

1

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.

Ahmed AU
  • 2,757
  • 2
  • 6
  • 15
0

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.

Luuklag
  • 3,897
  • 11
  • 38
  • 57
Drzemlik
  • 59
  • 1
  • 9