CSV IMPORT

[lucasnishimura]

hi Folks

I have a comma delimited file with some thing like this
03/08/05 18:05:16,Trouble,-----,Calling,Mute,, ,"test fild with some line breaks",wire,lucas nishimuraa,,PLUS

The double quoted field some times has line breaks and excel 2003 can't import it correctly.

However excel 2000 does.

Does any one have any clue about how to fix it?
Thanx in advance

 

[BrianB]

It helps having some existing code to adapt. Make sure you set the ExpectedNumberOfColumns variable etc. as required.

'=================================================
'- MACRO TO IMPORT COMMA DELIMITED TEXT
'- copes with line breaks in the data
'- and files larger than 65536 lines
'- Brian Baulsom August 2005
'=================================================
Sub IMPORT_COMMA_DELIMITED()
    Dim ExpectedNumberOfColumns As Integer
    Dim FileName As String
    Dim FileNum As Integer
    Dim ToRow As Long
    Dim ToCol As Integer
    Dim ws As Worksheet
    Dim TextLine As String
    Dim MyDelimiter As String
    Dim MyField As String
    Dim LineLength As Integer
    Dim c As String
    Dim n
    '==================================
    '- CHANGE AS REQUIRED *********
    ExpectedNumberOfColumns = 7
    FileName = "C:\TEST\TEST.txt"
    '==================================
    '- initialise
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set ws = ActiveSheet
    ws.Cells.ClearContents
    ToRow = 1
    MyDelimiter = ","  ' comma
    '-------------------------------------
    '- MAIN LOOP
    FileNum = FreeFile()
    Open FileName For Input As #FileNum
    ToCol = 1
    MyField = ""
    '==================================================
    '- import textfile line by line
    '==================================================
    Do While Seek(FileNum) <= LOF(FileNum)
        Application.StatusBar = _
            "Importing Row :  " & ToRow
        '-----------------------------------------------
        '- parse line
        Line Input #FileNum, TextLine
        LineLength = Len(TextLine)
        For n = 1 To LineLength
            c = Mid(TextLine, n, 1)
            If c = MyDelimiter Then
                ws.Cells(ToRow, ToCol).Value = MyField
                ToCol = ToCol + 1
                MyField = ""
            Else
                MyField = MyField & c
                ws.Cells(ToRow, ToCol).Value = MyField
            End If
        Next
        '============================================
        '- if expected number of columns then
        '- check row number for end of worksheet
        '============================================
        If ToCol = ExpectedNumberOfColumns Then
            If ToRow = 65536 Then
               Set ws = ActiveWorkbook.Sheets.Add ' add a sheet
               ToRow = 2
            Else
                ToRow = ToRow + 1
            End If
            '---------------------------------------
            ToCol = 1
            MyField = ""
        End If
    Loop
    '- finish ----------------------------------
    Close #FileNum
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    MsgBox ("Imported " & ToRow - 1 & " lines.")
End Sub

 

[jmward77]

Hello there...

I found this great code and tried giving it a whirl last night. After a few hours, it came back and said the string didn't have any available space, or something to that effect. I'm running a 3.4 ghz P4 with a gig of ram, so I don't believe it to be a system resource issue.

The file is a 316 mb txt/csv data feed. This is something I'll have to do regularly, so I'm trying to figure out something. I can make the file available for download if that would be any help.

Any assistance would be greatly appreciated!

Jeremy

 

[BrianB]

It may be that the text file contains unusual data.

1. Try adding line of code at the beginning

On Error Resume Next

2. Open the textfile in Notepad or some other text editor ang see what is in the line of text indicated by the row number in Excel StatusBar.

Sometimes we need to run Edit/Replace (with a space or empty string) to get rid of odd characters before inporting into Excel - especially if there are commas or whatever included in the data - where they shouldn't be.

3. If the above fail, Click Debug when error occurs and see which line of code gives the problem & let me know. Also more exact details of what happens.