text/csv file import - variable number of columns

[Darren]

Hi,

I am trying to import excel csv files into access. I can get the data I
want into a temp table easily enough. My trouble is that I am dealing with
survey data and the number of questions can vary from year to year (each
individual question is a column). Is there a way of writing a sort of
automated query so that I don't have to go in and manually update the code in
the append queries everytime with the new question numbers? i.e. somehow
take the tabledefinitions from the temp table and put them into the append
query?

Thanks for any ideas,

Darren

 

[Jamie Collins]

I am trying to import excel csv files into access. I can get the data I
want into a temp table easily enough. My trouble is that I am dealing with
survey data and the number of questions can vary from year to year (each
individual question is a column). Is there a way of writing a sort of
automated query so that I don't have to go in and manually update the code in
the append queries everytime with the new question numbers? i.e. somehow
take the tabledefinitions from the temp table and put them into the append
query?

You could interrogate the file's schema but it would be tempting to
simply use SELECT *. Note a csv file is Text rather than Excel format
e.g.

SELECT *
INTO NewTempTable
FROM [Text;HDR=Yes;Database=C:\My Folder\;].MyFile#csv;

Jamie.

--

 

[John Nurick]

Hi Darren,

If Jamie's message doesn't answer your question, tell us more about the
append query and the table it's appending to.