• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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
0
davetough
Asked:
davetough
  • 6
  • 6
  • 3
  • +1
2 Solutions
 
Helen FeddemaCommented:
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.
0
 
davetoughAuthor Commented:
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
0
 
simpsolCommented:
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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
davetoughAuthor Commented:
good info but i am trying to import csv file into access
0
 
simpsolCommented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
is this a one time operation or are you going to do this regularly?
what version of access are you using?
0
 
simpsolCommented:
When you are importing data using Access, during one of the steps of the Wizard change the column which is not importing to Text instead of the General as default and see if that helps.
0
 
davetoughAuthor Commented:
cap - i am being requested to start doing it everyday
0
 
Helen FeddemaCommented:
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.
0
 
Helen FeddemaCommented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
dave,
what version of access are you using?
0
 
simpsolCommented:
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)
0
 
davetoughAuthor Commented:
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
0
 
davetoughAuthor Commented:
am trying to import first field as text now
0
 
Rey Obrero (Capricorn1)Commented:
you have to create an import specification to do this using VBA codes
'
'to create an import specification for A2003
'
1. File>get external data>import
2. select in the File of types box   Text files (*.txt etc..
3. select the file
4. in the import text wizard window select  delimited
5. Click advanced
6. in the import specification window
    type the name of the field in the Field Name column
    (here you can use the field names of the destination table, specify data type,
      check the box Skip if you do not want to import the column)


7 click save as, give the specification a name  <-- this is the specification name that you will use in the command line below

Now you can use the code below to import the CSV file via code

   DoCmd.TransferText acImportDelim, "ImportSpecificationName", "myTableName", "C:\myCsv.csv", True



0
 
davetoughAuthor Commented:
thank you -
cap this will help alot - since i am going have to do it every day
0
 
simpsolCommented:
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.
0
 
simpsolCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now