Import CSV with comma for fields and semicolon for records

[JohnKauffman]

From another source I'm generating .txt with data in a string with records separate by semicolons and the two fields separated by a comma.
97,946;187,946;431,947;317,945;367,946;334,960;

I thought this would be an easy import where record=row and the two fields each get their own col. But during the import wizard when specifying the separation characters I don't see a way to indicate the distinction that comma is value separator and semicolon is record separator.
Anyone with a suggestion?

It is conceivable I could design source to create string with different separating characters as long as they are within lower ASCII.

Thanks.

 

[patel45]

Sub ReadCsv()
Filename = "C:\users\user\desktop\a.csv" ' <<<<<<<< change it
sn = Split(CreateObject("scripting.filesystemobject").opentextfile(folderPath & Filename).readall, ";")
For j = 0 To UBound(sn)
   sp = Split(sn(j), ",")
   u = UBound(sp)
   If u <= 0 Then u = 0
   Cells(j + 1, 1).Resize(, u + 1) = sp
Next
End Sub

 

[JohnKauffman]

thanks, Patel. I was looking for a way to do directly with the Excel import wizard. But VBA is cool. Black cat, white cat - whatever catches the mouse.

 

[Rick Rothstein]

thanks, Patel. I was looking for a way to do directly with the Excel import wizard. But VBA is cool. Black cat, white cat - whatever catches the mouse.

Here is another macro you can consider (it eliminates the call out to the File System Object and also uses no looping)...

Sub ReadCsv()
  Dim FileNum As Long, Filename As String, TotalFile As String, Records() As String
  Filename = "C:\temp\a.csv" ' <<<<<<<< change it
  FileNum = FreeFile
  Open Filename For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Records = Split(TotalFile, ";")
  With Range("A1:A" & UBound(Records) + 1)
    .Cells = Records
    .TextToColumns Range("A1"), xlDelimited, , , False, False, True, False, False
  End With
End Sub

 

[patel45]

Hi Rick, did you test it ?
I propose

Sub ReadCsv()
  Dim FileNum As Long, Filename As String, TotalFile As String, Records() As String
  Filename = "C:\temp\a.csv" ' <<<<<<<< change it
  FileNum = FreeFile
  Open Filename For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  TotalFile = Replace(TotalFile, ",", "|")
  Records = Split(TotalFile, ";")
  With Range("A1:A" & UBound(Records))
    .Value = Application.Transpose(Records)
    .TextToColumns Range("A1"), xlDelimited, , , False, False, False, False, True, "|"
  End With
End Sub

 

[Rick Rothstein]

Hi Rick, did you test it ?
I propose

Sub ReadCsv()
  Dim FileNum As Long, Filename As String, TotalFile As String, Records() As String
  Filename = "C:\temp\a.csv" ' <<<<<<<< change it
  FileNum = FreeFile
  Open Filename For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  [B][COLOR=#008000]TotalFile = Replace(TotalFile, ",", "|")
[/COLOR][/B]  Records = Split(TotalFile, ";")
  With Range("A1:A" & UBound(Records))
    .Value = [B][COLOR=#b22222]Application.Transpose([/COLOR][/B]Records[B][COLOR=#b22222])
[/COLOR][/B]    .TextToColumns Range("A1"), xlDelimited, , , False, False, False, False, True, [B][COLOR=#008000]"|"[/COLOR][/B]
  End With
End Sub

You are correct... I forgot the Transpose part (shown in red above) for that code line. Thank you for catching that.

As for replacing all the commas with pipe symbols (shown in green above) because if the commas were left in, the values would be placed in the cell as numbers with thousands separators and, hence, Text To Columns would not see them as delimited values.... I think it would be quicker to not do the replacement, rather, just format the cells as Text (using code) and then use the comma as the delimiter. Something like this...

Sub ReadCsv()
  Dim FileNum As Long, Filename As String, TotalFile As String, Records() As String
  Filename = "C:\temp\a.csv" ' <<<<<<<< change it
  FileNum = FreeFile
  Open Filename For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Records = Split(TotalFile, ";")
  With Range("A1:A" & UBound(Records) + 1)
    .NumberFormat = "@"
    .Cells = Application.Transpose(Records)
    .TextToColumns Range("A1"), xlDelimited, , , False, False, True, False, False
  End With
End Sub

 

[patel45]

[B][COLOR=#008000].NumberFormat = "@"[/COLOR][/B]

you are a fox !!!