PC Review


Reply
Thread Tools Rate Thread

Link CSV file created query append query to append data

 
 
=?Utf-8?B?ZXNwYXJ6YW9uZQ==?=
Guest
Posts: n/a
 
      1st Jul 2005
I have linked a csv file as a table. I generated an append query then I
generated a Make-Table query. I then get a message that says "The existing
table will be deleted before you run the query. This generates all the
information every time I run the query. My problem is the linked file does
not have 2 crucial fields that I need to be able to sort with and control the
output. So the csv file has info like grower number, cherry sizes, variety,
date, pool etc. I need to sort the data by a Run order field that I can
number 1,2,3, etc and then be able to use a data field yes/no to check off
what has been packed so it does not come back when I run the query again. any
ideas?
here is the SQL view
SELECT tblLotList.LotNum, [RSM-Recv Query].TicketNum, [RSM-Recv Query].Bins,
[RSM-Recv Query].RecvDate, [RSM-Recv Query].CullP, [RSM-Recv Query].BrineP,
[RSM-Recv Query].P080, [RSM-Recv Query].P085, [RSM-Recv Query].P090,
[RSM-Recv Query].P095, [RSM-Recv Query].P100, [RSM-Recv Query].P105,
[RSM-Recv Query].P110, [RSM-Recv Query].P115, [RSM-Recv Query].P120,
[RSM-Recv Query].P130, [RSM-Recv Query].PoolNum, [RSM-Recv Query].Variety,
tblLotList.Packed, tblLotList.[Run Order] INTO [Append Data]
FROM tblLotList INNER JOIN [RSM-Recv Query] ON tblLotList.LotNum = [RSM-Recv
Query].LotNum
WHERE ((([RSM-Recv Query].Variety)=[Enter Variety]));

 
Reply With Quote
 
 
 
 
=?Utf-8?B?R1BP?=
Guest
Posts: n/a
 
      1st Jul 2005
Are you relying on the csv file being presented to you in any particular
order? If so, you may want to add an incrementing surrogate key (or row id)
to the csv file before you import it. If this is what you want, post back.

Alternatively there may be a field in the CSV file that can be used to
determine order.

Database tables by definition, cannot be relied upon to store data in any
order. Queries (in particular the ORDER BY clause) determine the ordering of
data presented to the end user (you). So you will need to establish before
you start using the data, precisely what order you want, and how it could be
derived from data that could potentialy be stored in random order.

Actually on re-reading, it could be that the make-table action query is
blowing away useful information you've added after the import. Why not
append new data to the existing table, but uniquely identify the new data
with a metadata field like the date/time of import or the file name (if it is
unique) coupled with your existing method for unquely identifying each row of
data. So currently you may have:
Fld1 Fld2...Fldn in your CSV file and your query might look like:
INSERT INTO tblABC(F1, F2,...Fn)
SELECT Fld1, Fld2,...Fldn
FROM tblCSV
this might change to:
INSERT INTO tblABC(F1, F2,...Fn, FImportDateTime)
SELECT Fld1, Fld2,...Fldn, Now() as ImportDateTime
FROM tblCSV



"esparzaone" wrote:

> I have linked a csv file as a table. I generated an append query then I
> generated a Make-Table query. I then get a message that says "The existing
> table will be deleted before you run the query. This generates all the
> information every time I run the query. My problem is the linked file does
> not have 2 crucial fields that I need to be able to sort with and control the
> output. So the csv file has info like grower number, cherry sizes, variety,
> date, pool etc. I need to sort the data by a Run order field that I can
> number 1,2,3, etc and then be able to use a data field yes/no to check off
> what has been packed so it does not come back when I run the query again. any
> ideas?
> here is the SQL view
> SELECT tblLotList.LotNum, [RSM-Recv Query].TicketNum, [RSM-Recv Query].Bins,
> [RSM-Recv Query].RecvDate, [RSM-Recv Query].CullP, [RSM-Recv Query].BrineP,
> [RSM-Recv Query].P080, [RSM-Recv Query].P085, [RSM-Recv Query].P090,
> [RSM-Recv Query].P095, [RSM-Recv Query].P100, [RSM-Recv Query].P105,
> [RSM-Recv Query].P110, [RSM-Recv Query].P115, [RSM-Recv Query].P120,
> [RSM-Recv Query].P130, [RSM-Recv Query].PoolNum, [RSM-Recv Query].Variety,
> tblLotList.Packed, tblLotList.[Run Order] INTO [Append Data]
> FROM tblLotList INNER JOIN [RSM-Recv Query] ON tblLotList.LotNum = [RSM-Recv
> Query].LotNum
> WHERE ((([RSM-Recv Query].Variety)=[Enter Variety]));
>

 
Reply With Quote
 
 
 
 
=?Utf-8?B?ZXNwYXJ6YW9uZQ==?=
Guest
Posts: n/a
 
      5th Jul 2005
First of all thank you for your response. I need to accomplish 2 things. 1-
Be able to use a column (run order) to sort by number meaning I want a column
where I can assign a row with any given number (1,4,7,2,3,6,5,etc) then sort
numerically to come up with my run order in numeric sequence. Then I need to
use the "package" column to checkmark the fruit already run so that
information does not appear in my query. Now with my previous question I
can't accomplish this with the make table query.

"GPO" wrote:

> Are you relying on the csv file being presented to you in any particular
> order? If so, you may want to add an incrementing surrogate key (or row id)
> to the csv file before you import it. If this is what you want, post back.
>
> Alternatively there may be a field in the CSV file that can be used to
> determine order.
>
> Database tables by definition, cannot be relied upon to store data in any
> order. Queries (in particular the ORDER BY clause) determine the ordering of
> data presented to the end user (you). So you will need to establish before
> you start using the data, precisely what order you want, and how it could be
> derived from data that could potentialy be stored in random order.
>
> Actually on re-reading, it could be that the make-table action query is
> blowing away useful information you've added after the import. Why not
> append new data to the existing table, but uniquely identify the new data
> with a metadata field like the date/time of import or the file name (if it is
> unique) coupled with your existing method for unquely identifying each row of
> data. So currently you may have:
> Fld1 Fld2...Fldn in your CSV file and your query might look like:
> INSERT INTO tblABC(F1, F2,...Fn)
> SELECT Fld1, Fld2,...Fldn
> FROM tblCSV
> this might change to:
> INSERT INTO tblABC(F1, F2,...Fn, FImportDateTime)
> SELECT Fld1, Fld2,...Fldn, Now() as ImportDateTime
> FROM tblCSV
>
>
>
> "esparzaone" wrote:
>
> > I have linked a csv file as a table. I generated an append query then I
> > generated a Make-Table query. I then get a message that says "The existing
> > table will be deleted before you run the query. This generates all the
> > information every time I run the query. My problem is the linked file does
> > not have 2 crucial fields that I need to be able to sort with and control the
> > output. So the csv file has info like grower number, cherry sizes, variety,
> > date, pool etc. I need to sort the data by a Run order field that I can
> > number 1,2,3, etc and then be able to use a data field yes/no to check off
> > what has been packed so it does not come back when I run the query again. any
> > ideas?
> > here is the SQL view
> > SELECT tblLotList.LotNum, [RSM-Recv Query].TicketNum, [RSM-Recv Query].Bins,
> > [RSM-Recv Query].RecvDate, [RSM-Recv Query].CullP, [RSM-Recv Query].BrineP,
> > [RSM-Recv Query].P080, [RSM-Recv Query].P085, [RSM-Recv Query].P090,
> > [RSM-Recv Query].P095, [RSM-Recv Query].P100, [RSM-Recv Query].P105,
> > [RSM-Recv Query].P110, [RSM-Recv Query].P115, [RSM-Recv Query].P120,
> > [RSM-Recv Query].P130, [RSM-Recv Query].PoolNum, [RSM-Recv Query].Variety,
> > tblLotList.Packed, tblLotList.[Run Order] INTO [Append Data]
> > FROM tblLotList INNER JOIN [RSM-Recv Query] ON tblLotList.LotNum = [RSM-Recv
> > Query].LotNum
> > WHERE ((([RSM-Recv Query].Variety)=[Enter Variety]));
> >

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Excel VBA: sending output data table to created .csv file +append Options Mike Microsoft Excel Misc 1 10th Dec 2011 01:39 PM
Invalid CSV file (no Unicode ID) for csv file RTL Microsoft Windows 2000 Active Directory 1 21st Mar 2006 08:28 PM
How to use the exact format in a csv file when opening a csv file =?Utf-8?B?dG9uZw==?= Microsoft Excel Misc 1 13th Oct 2005 05:41 AM
SS# field after link to .csv created lndsy Microsoft Access External Data 2 30th May 2004 02:36 PM
Re: Data manipulation from .csv file - testfile.csv (1/1) - testfile1.csv (1/1) Keith A. Lees Microsoft VB .NET 0 12th Nov 2003 02:58 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:46 AM.