Trying to import a CSV file into a sheet named IMPORT

Question

Here is what my Txt file looks like... this gets exported via an old but useful tool:

Here is the code I found on Internet:

Option explicit

Sub ReadInCommaDelimFile()

Dim rFirstCell As Range 'Points to the First Cell in the row currently being updated
Dim rCurrentCell As Range 'Points the the current cell in the row being updated
Dim sCSV As String 'File Name to Import
Dim iFileNo As Integer 'File Number for Text File operations
Dim sLine As String 'Variable to read a line of file into
Dim sValue As String 'Individual comma delimited value

'Prompt User for File to Import
sCSV = Application.GetOpenFilename("CSV Files, *.TXT", , "Select File to Import")
If sCSV = "False" Then Exit Sub

'Clear Existing Data
ThisWorkbook.Worksheets("IMPORT").Cells.Delete
'wsData.Cells.Delete 'Use this method if you set the vb-name of the sheet

'Set initial values for Range Pointers
Set rFirstCell = Range("A2")
Set rCurrentCell = rFirstCell

'Get an available file number
iFileNo = FreeFile

'Open your CSV file as a text file
Open sCSV For Input As #iFileNo

'Loop until reaching the end of the text file
Do Until EOF(iFileNo)

    'Read in a line of text from the CSV file
    Line Input #iFileNo, sLine

    Do
        sValue = ParseData(sLine, "','")


        If sValue <> "" Then
            rCurrentCell = sValue 'put value into cell
            Set rCurrentCell = rCurrentCell.Offset(0, 1) 'move current cell one column right
        End If

    Loop Until sValue = ""

    Set rFirstCell = rFirstCell.Offset(1, 0) 'move pointer down one row
    Set rCurrentCell = rFirstCell 'set output pointer to next line
Loop

'Close the Text File
Close #iFileNo

 End Sub

 Private Function ParseData(sData As String, sDelim As String) As String
 Dim iBreak As Integer

iBreak = InStr(1, sData, sDelim, vbTextCompare)

If iBreak = 0 Then
    If sData = "" Then
        ParseData = ""
    Else
        ParseData = sData
        sData = ""
    End If
Else
    ParseData = Left(sData, iBreak - 1)
    sData = Mid(sData, iBreak + 1)
End If

End Function

Here is my result:

enter image description here

No matter what I try, I always get stuck with the Quote mark and Commas.

Here is the working code:

 Option Explicit

 Sub ReadInCommaDelimFile()
 Dim rFirstCell As Range 'Points to the First Cell in the row currently being updated
 Dim rCurrentCell As Range 'Points the the current cell in the row being updated
 Dim sCSV As String 'File Name to Import
 Dim iFileNo As Integer 'File Number for Text File operations
 Dim sLine As String 'Variable to read a line of file into
 Dim sValue As String 'Individual comma delimited value
 Dim sValue2 As String 'Individual comma delimited value



'Prompt User for File to Import
sCSV = Application.GetOpenFilename("CSV Files, *.TXT", , "Select File to Import")
If sCSV = "False" Then Exit Sub

'Clear Existing Data
ThisWorkbook.Worksheets("IMPORT").Cells.Delete
'wsData.Cells.Delete 'Use this method if you set the vb-name of the sheet

'Set initial values for Range Pointers
Set rFirstCell = Range("A2")
Set rCurrentCell = rFirstCell

'Get an available file number
iFileNo = FreeFile

'Open your CSV file as a text file
Open sCSV For Input As #iFileNo

'Loop until reaching the end of the text file
Do Until EOF(iFileNo)

    'Read in a line of text from the CSV file
    Line Input #iFileNo, sLine

    Do
        sValue = ParseData(sLine, ",")


        If sValue <> "" Then
            sValue2 = Left(sValue, Len(sValue) - 1)
            sValue2 = Right(sValue2, Len(sValue2) - 1)
            rCurrentCell = sValue2 'put value into cell
            Set rCurrentCell = rCurrentCell.Offset(0, 1) 'move current cell one column right
        End If

    Loop Until sValue = ""

    Set rFirstCell = rFirstCell.Offset(1, 0) 'move pointer down one row
    Set rCurrentCell = rFirstCell 'set output pointer to next line
Loop

'Close the Text File
Close #iFileNo

End Sub

Private Function ParseData(sData As String, sDelim As String) As String
 Dim iBreak As Integer

iBreak = InStr(1, sData, sDelim, vbTextCompare)

If iBreak = 0 Then
    If sData = "" Then
        ParseData = ""
    Else
        ParseData = sData
        sData = ""
    End If
Else
    ParseData = Left(sData, iBreak - 1)
    sData = Mid(sData, iBreak + 1)
End If

End Function

https://stackoverflow.com/questions/12197274/is-there-a-way-to-import-data-from-csv-to-active-excel-sheet... — BigBen, Nov 05 '19 at 17:01
one solution would be to add another conditional loop to remove quote marks and commas — JSRB, Nov 05 '19 at 17:02
Is the starting data all in a single cell or are the values like 'AAAA' each in their own cell? If all in a single cell then use TextToColumns. If in separate cells then do a Replace() to remove the unwanted characters. — SmileyFtW, Nov 05 '19 at 17:24
Your `ParseData` function is re-inventing existing Excel functionality. — BigBen, Nov 05 '19 at 17:40
Hi BigBen, The Issue is that I can't use the QueryTable because once I am done with the import, I need to convert the Data into a Table bur the QueryTable function creates a Table taet I can't release or Modify... But it does copy the Data the right way !! — Patrick Dumais, Nov 05 '19 at 18:01
Change `sData = Mid(sData, iBreak + 1)` to `sData = Mid(sData, iBreak + Len(sDelim))` — chris neilsen, Nov 05 '19 at 18:09

score 0 · Answer 1 · answered Nov 05 '19 at 17:19

0

Try adding this above "sValue = ParseData(sLine, "','")" to remove the single quotes

sLine = Replace(sLine, "'", "")

answered Nov 05 '19 at 17:19

Jeff

53
5

It seems to come fron the ParseData finction... Here is what I get now...'5555' so Before and After... If I watch the Value before the ParseData, they are not there. – Patrick Dumais Nov 05 '19 at 17:41

score 0 · Answer 2 · answered Nov 05 '19 at 20:37

Your last code iteration indicates that your CSV file is saved as a *.txt file.

If that is really the case, you could open it using the Workbooks.OpenText method which would allow you to properly parse the data, including handling the singlequote text qualifier character.

This will not create a table as does the QueryTables method.

Then copy the data from this newly opened workbook to your IMPORT worksheet in your present workbook.

For example:

Option Explicit
Sub ReadInCommaDelimFile()
    Dim sCSV
    Dim WB As Workbook, dataWS As Worksheet
sCSV = Application.GetOpenFilename("CSV Files (*.txt),*.txt", , "Select File to Import")
    If sCSV = False Then Exit Sub

ThisWorkbook.Worksheets("IMPORT").Cells.Clear

Application.ScreenUpdating = False
Workbooks.OpenText Filename:=sCSV, _
        textqualifier:=xlTextQualifierSingleQuote, _
        consecutivedelimiter:=True, _
        Tab:=False, _
        semicolon:=False, _
        comma:=True, _
        Space:=False, _
        other:=False

Set WB = ActiveWorkbook

Set dataWS = WB.Worksheets(1)

dataWS.UsedRange.Copy ThisWorkbook.Worksheets("IMPORT").Range("A2")

WB.Close savechanges:=False

End Sub

Trying to import a CSV file into a sheet named IMPORT

2 Answers2