Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Finland
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing csv files to Excel (2002)

    A customer needs to edit some data held in our database through Excel. We have released it as a CSV file, but Excel insists on making the telephone code column into number format, thereby dropping the leading 0s. I realise that you can force the imort wizard from the data menu, but this is quite long winded. My temporary fix to them (which works but I hate <img src=/S/barf.gif border=0 alt=barf width=64 height=23>!) is to make the leading zero a capital o (adding a space at the end of the three figure code doesn't work). Is there any other way to make Excel accept the three figure code as text.

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

    Re: Importing csv files to Excel (2002)

    I tried several things and, if you can control the creation of the CSV file, is to put a single quote in front of the number. This will import into Excel as a text value, but will display the single quote in the cell. Now, select the column and then click on Replace in the Edit menu. Replace the single quote with a single quote. This will keep the cells as text, but hide the single quote. This could be automated with a macro if you want.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Finland
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing csv files to Excel (2002)

    Oh thanks for looking at this for me. Unfortunately, the contents of this file are displayed in the database and this would show up the single quote - I wonder if there is a non printing character that could be entered which Excel would interprete as text, but would not appear when displayed as plain text from the database.

    I also wondered if it is possible to turn off Excel turning imported numbers into numbers at import. (Does that makes sense?)

    Your thoughts are much appreciated.

    Val

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

    Re: Importing csv files to Excel (2002)

    OK, then another possibility is to use a Macro to do the import. I recorded the following macro to import my test CSV file which had the "telephone number" in column 1 of the CSV file:

    <pre>Sub ImportCSV()
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:Wo rkTest.csv", _
    Destination:=Range("A1"))
    .Name = "Test"
    .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 = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2, 1, 1)
    .Refresh BackgroundQuery:=False
    End With
    End Sub
    </pre>



    You would have to modify it to handle specifying the file to be imported (you could use the GetOpenFileName method to get the file name from the user if you don't have a constant file name and directory), and the sheet where you want the data imported (the macro imports to the active sheet). You would also have to modify the TextFileColumnDataTypes to specify which column contains your "telephone number".
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing csv files to Excel (2002)

    Hi There

    I may be misreading your issue, so apologies in advance... Are you trying to Import INTO Excel or Export FROM Excel?

    If you are Importing INTO Excel.... Excel Menu... Data, Get External Data, Import Text File.... This allows you to specify how you want the imported fields treated.

    If your are trying to Export FROM Excel... File Save As, and select TAB delimited. This for some reason maintains numeric formats (leading zeros), whereas comma separated doesn't. N.B. When you re-open a tab delimited file you are autmatically taken through the Get External Data dialogue where you seem to have to choose text format for the fields, or you lose your leading zeros.

    Alternatively.... Use the =TEXT(CellRef,Format_Text) function to move the data to another sheet and then save that file?

    Hope this helps some.

    Regards
    Peter

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Location
    Finland
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing csv files to Excel (2002)

    Peter - The customer has a file which they wish to edit in Excel and import to our database. We suggested CSV format and have now coded it in. We were just a little miffed that this couldn't be a simple operation that anyone could do. The fudge around using a capital o means there is no hastle they open the file, edit and we just pick it up. The reason I don't like it is that it is bound to be forgotten at some time where the whole inventing the wheel again thing will happen. Using tab delimited does mean that Excel saves the leading 0, but its still a hasstle to go through the import wizard each time

    Legare - thanks for the macro which does the job. It still seems to be using a sledge hammer to crack a nut - but nothing is perfect in computing!

    Thanks for all your help!

    Val

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

    Re: Importing csv files to Excel (2002)

    Never use force. Get a bigger hammer! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    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
  •