text/csv file import - variable number of columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darren - 22 Nov 2004 22:49 GMT

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

Reply to this Message

Jamie Collins - 23 Nov 2004 09:30 GMT

> 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
[quoted text clipped - 4 lines]
> 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.

--

Reply to this Message

John Nurick - 23 Nov 2004 21:54 GMT

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.

>Hi,
>
[quoted text clipped - 10 lines]
>
>Darren

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message