Importing CSV file

[mark hansen]

I have a csv file from another server. When I open it with Excel (double click or File ->Open) it opens as expected.

The problem is I need to import this file as part of another workbook and read parts of the information with formulas into other worksheets. When I import (Data->From Text) and set for comma separated, it doesn't come in correctly.

One thing I noticed, is the text has quotes around them with a comma in between the ending quote and the beginning quote of the next column. When I import, what MAY BE happening is the end of line isn't being looked at in the same way when opening as a CSV file.

(Option 1)I think I would prefer to have the CSV come into a separate tab of my main workbook and pull the numbers from there. (but that's causing problems so far)
(Option 2)I guess I could have a macro in the main workbook open another workbook that opens the CSV file and have formulas pull numbers from the other workbook.

I prefer Option 1 to make things simpler, any ideas what might be going on to cause the problems with Data->From text steps. if not, I'll go with option 2.

Thanks for any ideas,
Mark

 

[Fluff]

When you use from Text, what settings are you using?

 

[mark hansen]

Here is a recorded macro of my import

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;\\XXXXXXXX\MyCSV.csv" _
        , Destination:=Range("$A$1"))
        .Name = "RTTreatSheet"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

 

[Fluff]

That's the same as I got when I recorded a macro & the file is imported correctly. It may have something to do with the csv you trying to import.
Would you be able to share the csv via OneDrive, or DropBox?

 

[mark hansen]

Not really, it has private information. Fluff, I appreciate you looking into this, I think I will need to go with option 2, use code to open the CSV file, have formulas in my main sheet add the necessary numbers and I'll use those numbers...

Thanks again,
Mark

 

[Fluff]

How about a halfway house, open the csv as a new file & then copy the data into another sheet in your main workbook?

 

[mark hansen]

yea, I'm looking at that sort of. Once I have the csv open (Since I need to anyway) I don't think I will need to copy it in... Formulas should be able to look into the open CSV file to get my numbers. Then close it. I may have the open event also open the CSV file, then close it. Once its closed the number should stay. I need to look into a button that will reopen the CSV and close it, as an update, but it will only need to be opened at shift change so they can get updated numbers to assign the oncoming shift accordingly.

Thanks for you ideas, it's always helpful to get other ideas.
Mark