Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing Excel to Import .CSV data
Does anyone know if it is possible to configure Excel to recognize
that when opening a .CSV file, the Import Wizard should be used? We use a lot of .CSV files here, and many users have problems when they double-click the file icon to open it. If there are columns with numerical data, excel will convert them to numbers and then will often change them by truncating leading zeros, or converting them to exponential values if they are many digits. The standard solution for this is to open Excel and then initiate the import wizard, but it would be nice if Excel could be set to automatically recognize, by file type, that the import wizard should be used. I am reasonably capable with Excel myself and doubt that this option exists, but the expert knowledge within this forum gives me hope that if this can be done, someone here will know how. Thanks very much, TK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing Excel to Import .CSV data
Change the extension to .txt and open in Excel............the text
import wizard will appear. Gord Dibben Microsoft Excel MVP On Wed, 24 Aug 2011 07:38:50 -0700 (PDT), Cortez wrote: Does anyone know if it is possible to configure Excel to recognize that when opening a .CSV file, the Import Wizard should be used? We use a lot of .CSV files here, and many users have problems when they double-click the file icon to open it. If there are columns with numerical data, excel will convert them to numbers and then will often change them by truncating leading zeros, or converting them to exponential values if they are many digits. The standard solution for this is to open Excel and then initiate the import wizard, but it would be nice if Excel could be set to automatically recognize, by file type, that the import wizard should be used. I am reasonably capable with Excel myself and doubt that this option exists, but the expert knowledge within this forum gives me hope that if this can be done, someone here will know how. Thanks very much, TK |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forcing Excel to Import .CSV data
On 24/08/2011 19:45, Gord wrote:
Change the extension to .txt and open in Excel............the text import wizard will appear. Gord Dibben Microsoft Excel MVP On Wed, 24 Aug 2011 07:38:50 -0700 (PDT), wrote: Does anyone know if it is possible to configure Excel to recognize that when opening a .CSV file, the Import Wizard should be used? The problem here is that your CSV files are malformed. Quotes around numerical data held as strings have to be escaped into the CSV file. We use a lot of .CSV files here, and many users have problems when they double-click the file icon to open it. If there are columns with numerical data, excel will convert them to numbers and then will often change them by truncating leading zeros, or converting them to exponential values if they are many digits. If numeric data with many digits and leading zeroes is intended to stay as strings it must be represented in a CSV file as """000001234""" The standard solution for this is to open Excel and then initiate the import wizard, but it would be nice if Excel could be set to automatically recognize, by file type, that the import wizard should be used. I am reasonably capable with Excel myself and doubt that this option exists, but the expert knowledge within this forum gives me hope that if this can be done, someone here will know how. I think the problem lies elsewhere. The files may have commas to delimit the data fields but they are not correct CSV files. Copy and paste these into a black worksheet and save as CSV QWERTY 3.1 1234 "0001234" You will find that saved as CSV it will come out as QWERTY,3.1 1234,"""0001234""" XL2007 gets this right when you use Save As .CSV so I would say the problem lies here with whatever you are using to export malformed files with a .CSV extension. Renaming them as .TXT files would get the wizard but a better solution would be to fix the defective data source. Regards, Martin Brown |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
PLEASE!! How can I un-grey the 'Data-Import External Data-Import Data' option ??? | Excel Programming | |||
Checking & Forcing Data Entry in Cells | Excel Discussion (Misc queries) | |||
Forcing data into a cell | Excel Worksheet Functions | |||
Forcing data value in a cell | Excel Discussion (Misc queries) |