Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import formats on CSV files (All)

    I cannot find any way to get Excel to ask me for the format of aCSV file, short of changing the file extension!

    If I import a text file, Excel will ask me for the formats of the columns, but CSV files it automatically assumes a format for each field. Given that I have got fields which may have a leading zero and may be numeric, I get extremely frustrated that Excel lops the leading zero off every time. Even worse is when it decides that my numeric reference is a big number and displays it as an exponential. Even even worse is when I have some fields which are <numeric>E<number> as a (perfectly valid) reference ID. Excel assumes that those are exponentials and converts the E<number> into a perfectly spurious set of additional zeros.

    Aargh!

    Added to which, if you import a file and change something else and save it, Excel then saves what is displayed, rather than what the underlying data is. So if a long number was displayed as an exponential, it will be saved as an exponential, thus losing any richness in the data!

    Double aargh!

    So why does Excel do that? Why can't I specify the format of CSV files? And how can I make it do it (short of changing the file extension or unlinking the CSV extension from Excel)

    Cheers.

    Stuart

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import formats on CSV files (All)

    You can do it in VBA with something like this:

    <pre> With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:Wo rkDOS.csv", _
    Destination:=Range("A1"))
    .Name = "DOS"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .Refresh BackgroundQuery:=False
    End With
    </pre>


    Or if you want to do it from the menu, select "Get External Data" from the Data menu, then select "Import Text File" from the fly out menu.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import formats on CSV files (All)

    This example code has been a useful starting point for me but...

    My CSV file is includes both text and numbers for example, "name","add1","add2","15.20","25.360". I am well aware that the trailing zeros are not mathematically significant but I wish to retain them for presentation purposes. My spreadsheets are already set up to hold such 'numbers' as text while doing the maths on the values. Thus I wish to import the CSV as all text. I expected that the line:
    .TextFileTextQualifier = xlTextQualifierDoubleQuote

    in conjunction with a line like:
    .TextFileColumnDataTypes = Array(xlTextFormat)

    would mean that anything inside double quotes (i.e. everything) would end up as text in columns formatted as text and thus the trailing zeros would be retained. Unfortunately the Array(xlTextFormat) bit only seems to set the first column, rather than all columns, to text. If the fix is to specify a column type for each field that's fine for a specific CSV with a given number of fields but not all my CSV's have the same number of fields so how can I handle that?

    stuck

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Import formats on CSV files (All)

    I would think the easiest thing would be to not associate CSVs with excel, so when you open them you would get the wizard.

    Changing the extensions to txt would not be difficult before importing.

    If those are not feasible, You would have to create your own parsing routine. It could be done many different ways, most likely to open the text file, loop thru each line, read the line, go char by char reading and looking for the delimiters and qualifiers and parsing as desired. You can parse into an array and ensure that the array is formatted as a string.

    If you only are going to do comma delimited with dbl-quote qualifiers and make everything text it is not too involved, but if you need more "bells and whistles" it can get very complex.

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import formats on CSV files (All)

    I think that what you want is:

    <pre> .TextFileColumnDataTypes = Array(xlTextFormat,xlTextFormat,xlTextFormat,xlTex tFormat,xlTextFormat)
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •