Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1381
  • Last Modified:

transfer delimited by "^".csv into access table using vba

I have a large  .csv file which is delimited by "^" symbol. I need to transfer it to access database using VBA

I found the method below, but want a pure vba solution. is it even possible?

DoCmd.TransferText acImportDelim...
You need to create an Import Specification to do this automatically
To create the Import Specification
1) Click on external data> text file which then opens another window called "Get external data - Text file"
2) Use radio button to select "Import the source data into a new table in the current database"
3) specify the source of the file using the browse procedure then click OK
4) Choose radio button to select delimited format and then click next
5) this window allows you to choose delimiter and text qualifier and if first row contains names - click next
6) This window allows you to type the name of the field in the Field Name column, choose data type, and if you want field indexed - also can choose to skip field - then click next
7) This window allows you to add primary key or designate field as such - click next
8) Click on ADVANCED button
9. 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)
10 click save as, give the specification a name <-- this is the specification name that you will use in the command line below

DoCmd.TransferText acImportDelim, "ImportSpecificationName", "TableName", Returnvalue, blnHasFieldNames
 
0
maximyshka
Asked:
maximyshka
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
<but want a pure vba solution. is it even possible? >

yes, as stated above, after creating the Import Specification,

you can use the command

DoCmd.TransferText acImportDelim, "ImportSpecificationName", "TableName", "c:\folder1\myCsv.csv", true
0
 
maximyshkaAuthor Commented:
is there any way to do it without using Import specifications?
0
 
Rey Obrero (Capricorn1)Commented:
<is there any way to do it without using Import specifications? > Sorry but NO.
0
 
ste5anSenior DeveloperCommented:
is there any way to do it without using Import specifications?

Sure, use LINE INPUT and parse it manually. But I'm quite sure, this is a road you don't want to take.
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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now