Importing delimited files into Access

[denisel]

Hi,

We will be conducting surveys through SurveyMonkey online and will be importing the answers by tab delimited or comma delimited file into access. I was wondering if there is specific way to import delimited files into Access or Is there a specific way I should design the survey online so Access can recognize the file?

Here is my table structure to give you an idea on where I'm coming from. Thank you so much in advance!!!


tblPerson
PersonID (PK)
FirstName (text)
LastName (text)
Company(text)
Department(text)
Date (date/time)


tblPersonAnswer
PersonAnswerID (PK)
PersonID (text)
QuestionID (number)
AnswerChoiceActual (number)
AnswerChoiceImportance (number)


tblQuestions
QuestionID (PK)
QuestionNumber (text)
QuestionText (text)

[NeoPa]

For a delimited file you don't need much.
You can probably get away without an Import/Export spec.
It is a good idea to have a table properly predefined to hold the imported data though.
The design should not be affected by the means of transferring the data into the database, just by what you need it to do.

[MMcCarthy]

Hi,

We will be conducting surveys through SurveyMonkey online and will be importing the answers by tab delimited or comma delimited file into access. I was wondering if there is specific way to import delimited files into Access or Is there a specific way I should design the survey online so Access can recognize the file?

Here is my table structure to give you an idea on where I'm coming from. Thank you so much in advance!!!


tblPerson
PersonID (PK)
FirstName (text)
LastName (text)
Company(text)
Department(text)
Date (date/time)


tblPersonAnswer
PersonAnswerID (PK)
PersonID (text)
QuestionID (number)
AnswerChoiceActual (number)
AnswerChoiceImportance (number)


tblQuestions
QuestionID (PK)
QuestionNumber (text)
QuestionText (text)

Use a csv file and import it the first time using the Import wizard.

While the wizard is open click on the advanced button and save the import specificate with a name. e.g. My Import Spec

Create a new table during this import and call it something like tblImportFile

Then for future imports you can use the following code:

DoCmd.TransferText acImportDelim, "My Import Spec", "tblImportFile", "Full path to csv file", True

The True at the end means that the csv file has headings on the first line. Set it to false if it doesn't

Once the file is imported you can create append queries to append your data to the appropriate tables.

Note:
You must delete all records from the tblImportFile table before the next import.