text field in query

[thegooser]

Hi all.

I have 32 different csv files all with the same fields. I am looking at linking all of the csv files to access. I would then like to produce one query with all of the data inside.

The only problem I am having is that would like a field in the final query to tell me which csv file it is from.

Is there anyway for me to add a field in a query with a pre set text value. For example, in the final cell of every record in csv1 I would like it to say csv1.

As it will work with ever changing csv files, I need a linked text file so I can't do anything with the table itself.

Does anyone know what I am going on about, and if so how I can solve this?

 

[Addyman]

You can easily just get your query to display a value in a 'column' if required. In your query design view, you could add the following to the next available 'column' in the 'Field' line:

FileName: "csv1"

This would display csv1 against every record.

However, if you need to somehow determine the filename that you are querying from, that's more work

 

[Scotbot]

Would you be able to add a field to each of the source csv files with a reference, that will be read into Access and tell you which file it came from?

 

[MSAccessRookie]

Addyman has the right answer. The only thing that I might do differently is to make it a UNION Query, becasue you said "I would then like to produce one query with all of the data inside".

 

[Pat Hartman]

Would you be able to add a field to each of the source csv files with a reference, that will be read into Access and tell you which file it came from?

Addyman's suggestion assumed that you had a saved query for each CSV file and so each query would have a unique value for FileName - ie csv1, csv2, csv3, etc. How you accomplish this depends on how you automate the process. If you write code to link to all the CSV files in a particular directory. You can create a tempfile in the process and then use that to create a union query. I am a little leary of trying to automate something that creates a union query because I have had trouble with them in the past not being able to handle more than a few unions. I would probably create a temporary database (I don't want to bloat my code database). I would then create a table in that database to hold the imported data. My loop would then append the data one CSV file at a time. Between appends, I would run an update query that set the "FileName" column to a unique value for that file for all the rows where filename was empty (which would be only the rows just appended). The end result would be a table that contained all the data that I wanted to analyze including a source file identifier for each record. I would then link to that table and go about my analyzis.