importing a csv automatically not splitting each row

[craigey]

Hi guys,

I'm trying to import a csv file to Excel & forcing the cells to be text fields so that numeric values don't loose leading zero's. Unfortunately the code I have is importing all of the lines in the csv to row 1. I would apreciate some help to show me what I am missing to split the file correctly.

Sub import()
    Dim myF As String, txt As String, x, a(), n As Long, ff As Integer, i As Long
    myF = "C:\test.csv"
    ff = FreeFile
    Open myF For Input As #ff
    
    Do While Not EOF(ff)
        Line Input #ff, txt
        x = Split(txt, ",")
        n = n + 1
        ReDim Preserve a(1 To n)
        a(n) = x
    Loop
    Close #ff
    With ThisWorkbook.Sheets(1)
        .Cells.NumberFormat = "@"
        With .Range("a1")
            For i = 1 To n
                .Offset(i - 1).Resize(, UBound(a(i)) + 1).Value = a(i)
                .Offset(3, 1).Select
               
            Next
        End With
    End With
End Sub

 

[SkipVought]

hi,

Simply IMPORT the file using native Data > Get External Data > From Text

With this method, you can specify the TYPE of each column.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[craigey]

Thanks SkipVought, but i am trying to avoid using the data import as it is a very slow & tedious process to change all the columns to a text format rather than General.

The code does the import & sets the field to Text, correctly. The only problem is that all of the data in the CSV is transferred to only the top row. The plan was to have each row of the CSV on a new row of the Excel file.

 

[jges]

Record a macro while doing it Skip's way, then compare the macro code to what you have.

 

[SkipVought]

You really do not necessarily need to record a macro.

The query table is embedded in the sheet and if you REFRESH, the very same criteria will be applied to each column of each import.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[craigey]

Thanks I never knew the dataformat was stored, but for the sake of learning, I'd like to know why the existing code doesn't work as expected. I'm sure I need to split the line input at each line break (vbcrlf), but wont this break the splitting of the values into columns? Alos I'm not sure how to get the code to move to the next column at the line break.

 

[SkipVought]

Why use the wrong tool?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[craigey]

because it's for someone who is IT illiterate. If I can get this to work correectly they can just click on the big 'Import' button & everything is ready for them.

 

[SkipVought]

Click a button that does a simple

ActiveSheet.ListObjects(1).QueryTable.Refresh

KISS

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[craigey]

Thanks & that's probably the correct way of doing it, but I got it working late last night.

 

[SkipVought]

...and your solution was?

In order to bring this thread to a proper conclusion, please share with other Tek-Tips members how you achieved resolution.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[craigey]

just posting as much help as I recieved.

 

[craigey]

Apologies, that was childish.
I ended up opting to use an alternative script, which worked as I wanted it to.
I understand the KISS principle, but was eager to learn how the original code could be corrected, even if it was the wrong & most impractical way to achieve what I was attempting.