davetough
asked on
importing csv file into access
If i open excel:
to convert csv file to excel- all i do is:
goto data menu-
Select -From Text-Delimited- Semicolon-finish and ok
and the file gets converted how i want-
I cannot do this in Access- i import it in and recieve errors and first field-is not included.
Can Access convert file as efficiently as access?
am I doing something wrong?
thank you
to convert csv file to excel- all i do is:
goto data menu-
Select -From Text-Delimited- Semicolon-finish and ok
and the file gets converted how i want-
I cannot do this in Access- i import it in and recieve errors and first field-is not included.
Can Access convert file as efficiently as access?
am I doing something wrong?
thank you
Helen Feddema
What errors? We need more details. Can you post the .csv file? If it is just a matter of the wrong data type, this can be fixed by importing to a temp table, and doing the data type conversion in a query before appending data to one of your tables.
ASKER
unfortunately all data - private- only thing can tell you is first name EventTime;
access does not like and errors lie with that -
now it is importing everything except things in first field- or should say before first semi-colon- if that is right explanation-
strange to me - because excel doesn't have a problem
not sure this helps at all
thank you
access does not like and errors lie with that -
now it is importing everything except things in first field- or should say before first semi-colon- if that is right explanation-
strange to me - because excel doesn't have a problem
not sure this helps at all
thank you
If it is just a data from the query you are trying to convert then you can use the following steps
Run the Query or open the table as needed
Use Edit | Select All Records | Copy
Open Excel, Move to Cell A1
Use Edit | Paste and paste into excel.
Save as CSV.
From Access, you can click File | Export
Use the Save As Type Drop Down and Select Excel and then convert it to CSV
If you are trying to do it with code, just google Access to CSV and you will find several articles on the topic. One of the link is below
http://timothychenallen.blogspot.com/2007/08/vb-convert-any-access-table-to-csv.html
Hope this helps
Run the Query or open the table as needed
Use Edit | Select All Records | Copy
Open Excel, Move to Cell A1
Use Edit | Paste and paste into excel.
Save as CSV.
From Access, you can click File | Export
Use the Save As Type Drop Down and Select Excel and then convert it to CSV
If you are trying to do it with code, just google Access to CSV and you will find several articles on the topic. One of the link is below
http://timothychenallen.blogspot.com/2007/08/vb-convert-any-access-table-to-csv.html
Hope this helps
ASKER
good info but i am trying to import csv file into access
In Access you can use
File | Get External Data | Import
Browse to the Directory where you csv file is
From the File of Type Drop down Select Text Files (*.txt;*.csv;*.tab;*.asc)
Click Import and follow the wizard.
File | Get External Data | Import
Browse to the Directory where you csv file is
From the File of Type Drop down Select Text Files (*.txt;*.csv;*.tab;*.asc)
Click Import and follow the wizard.
is this a one time operation or are you going to do this regularly?
what version of access are you using?
what version of access are you using?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
cap - i am being requested to start doing it everyday
If EventTime is supposed to be a time (in Access, a Date/Time field), do as simpsol suggests to import it as text, then use CDate() to convert it back to a Date/Time value, at least if you can provide a date. Access Date fields always contain both a date and time, though you can format them to display only part of the entire date/time value. In some cases, if you just need the time and not the date, you may wish to leave it as a Text field.
Once you get the import specs done, and possibly a query to do data type conversion, you can make a function to do the import, using the save specs, and run an update or append query, and then run call the function from a macro or another procedure, as often as you wish.
dave,
what version of access are you using?
what version of access are you using?
If you are requested doing this every day then you can actually save the import by clicking in the advance button on the wizard screen and save the spec so that you do not have to change the formatting every day. If you would like to automate this, I can write you a program for a fee which will do all the work for you. (If you are interested)
ASKER
i have capability to use access 2003 or access 2007 -
to be honest lately been using to 2003 to create databases and i convert them after initial database created - becuase not conformtable w/2007 yet
to be honest lately been using to 2003 to create databases and i convert them after initial database created - becuase not conformtable w/2007 yet
ASKER
am trying to import first field as text now
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you -
cap this will help alot - since i am going have to do it every day
cap this will help alot - since i am going have to do it every day
I think you should give me some credit for the information I provided. I actually provide with all the information insteadl of typing each step as capricorn1 did.
Sorry for the early post, I see that you have given me assisted points and that is ok too. I got the email after I wrote the previous comment