The best way to access text(csv)?

Posted on 2005-02-25
Medium Priority
Last Modified: 2013-12-25
I would like to use MS Access to place different parts of a csv file into several tables.
One problem is the csv, part of it one (array if you will) would be a col and some PARTS will be rows(the data structure shows this)
Another is no headers.
File structure is;

sample of part of one row,if a array it would have 1435 arrays
"AQU","20010207", 1, 1,,1760,"d",,"S","BON",,42000,,,95.60," DSENTRIES 1ST AQU FEBRUARY 7 STATE-BRED 8F K42000 0 BON 1 MILE INNER DIRT THOROUGHBRED OPEN 3 YEAR OLDS MAIDEN SPECIAL WEIGHT PURSE $42000 (12:30) (11)","WYNN WITH QUINN;MAJESTIC SEA;NO WHITE FLAGS;BELIZE;CAPTAIN NICHOLAS;R. F. BURTON;JUSTIN CHARGE","","","","AQU", 1,,9,,,,"MATTIES GREGG M",9,0,1,0,"MESSINA R",,27,1,2,1,"MATTIES PAUL J JR",,,,"1", 30.00,"WYNN WITH QUINN",98,3,,"G","GR/RO",120,"FOREVER SILVER","SILVER BUCK","LUCKY STAFF","DARN THAT ALARM","Paul Matties Jr","NY",,,,,1,1,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,1,0,0,0,0,2000,3,0,0,0,0,1999,0,0,0,0,0,3,0,0,0,0,"20010203","20010129","20010124","20001210","20001205","20001129","20001121","20000810","20000805","20000726","20000713","20000707",51.4,52.0

The csv file is in e:\rTemp
Thru ODBC I made a Microsoft text driver [*.txt;*.csv] DSN "brisSingle"  (userDSN)
File name is AQU20010207.drf(csv)
Is this a good start?
I want to start a menu with several buttons(I think,but not sure,I can do)
I think most of the code should be in modules but would need examples.
I know the header names, but I would Like to use f1 f2 or col1 col2 etc. to reference the csv file.
Can this be done?
I have vb studio.net also so if that is easer to do maybe a exe file then use Access?
(I took one class in vb.net,they gave me the vb program,it proved to me I could never be a programer)
My level is novice so I would appreciate detailed code if possible.
Can I use SQL?
I have many books covering VBA,VB.net vb databases,ACCESS and SQL;Istill need help
Question by:afreer2
  • 7
  • 6
LVL 66

Expert Comment

by:Jim Horn
ID: 13412857
>part of it one (array if you will) would be a col and some PARTS will be rows
Not sure what you mean here.

If your file specifications do not change throughout the whole .csv file, then the best thing to do would be to import it into your Access app using the following VBA line...

docmd.TransferText acImportFixed, ,"Table Name", "e:\rTemp.csv", fieldnames true or false

Change "Table Name" to be a local Access table you want this data stored in.

Then, once the data is in a table, you can use queries to 'place different parts ... into several tables' all day long.

If you wish, you can explicitly define your file structure by creating an Import Spec (File : Get External Data : Import, choose your file, hit the Advanced button, then follow the prompts", then in the VBA line above you can state the import spec in the space between the acImportFixed and the table name (i.e. between the commas)

Hope this helps.

Author Comment

ID: 13415415
The parts mean the first 200 cols could be rows if the csv were only 200 col or so, the next 10 in sequence in that row are a col. (see http://www.brisnet.com/cgi-bin/static.cgi?page=drfsff)
THe first 101 cols would be a row and I would name the headers C1 to C101 because I only use some of the cols and would name the header in SQL.
TABLE2(workouts) 102 - 113 would have a header(I would have to supply in my table as dateWO). for col 1
114-125(timeWO) col2  126-137 (TrackWO) etc.
Access has a 255 field(col) limit. My fields,if this csv were in sequence,would be 1435
Does TransferText acImportFixed have this limit?
My fields vary from 3 to 255 chars. Is this fixed field?(the indivdual fields are always the same.)
When I pressed guess when seting up the ODbC it made this schema.int
When I set the ODBC with FILE - IMPORT EXTERNAL I got the message I can't do it.
I've been a member of EE for a month or so and this is all new to me; many times I need
to know where I Have to put code,in modules or buttons or some where else.
Even so,if I have to figure out what EE experts are trying to show me,I still appreciate the help.

Author Comment

ID: 13418112
The error message is 3423 and I searched solutions and they say I can't use Access.
I must have to use another platform?
I have Ms enterprise mgr. I know even less about that than vb.net
The question may be wrong also. I hope I gave enough info to express what I need;
that is access csv file and place parts of that csv file into many tables.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 46

Expert Comment

ID: 13419275
Since you are limited to 256 columns with MSAccess, you will NOT be able to
do what you want to do.  Even if you exported this to SQL Server, you would
encounter a 1024 column limit per table and would be unable to store this
as you wish.

What do you need to do to this data with MSAccess?

* Go through some normalization process to break up the data into logical/physical

* If you have MSAccess2000 or later, you can use the SPLIT() function to parse
   the line into an array.  The array will have 1435 positions.

* Decide what subset of the data is useful to you and only store that in MSAccess

* Do what you need to do to the data without using MSAccess tables or queries.


Author Comment

ID: 13422816
I thought MS Access would be easer for me to learn and it is a smaller program than vb.net.(less overhead in MB)
When I get the data into smaller tables(this is my main problem,I don't kow how to do it) I will query the tables to handicap.

aikimark ;What is SAS?

I can go through some normalization process to break up the data into logical/physical  parts?

* Decide what subset of the data is useful to you and only store that in MSAccess

These are the tables as in the csv file
*** Today's Horse/Trainer/Jockey/Owner ***
*** Horse History Data ***
*** Current Horse Stats ***
*** Horse's Past Performace Data for last 10 races ***
1264 o 1435 is a table also
(see http://www.brisnet.com/cgi-bin/static.cgi?page=drfsff)

I thought when I asked this same type of question before I could somehow put the csv file in memory then put the data into
tables and for some reason I thought I could create the tables as needed.

Even if I created tables before hand I still don't know how to put the data from the csv file.

FYI In horse racing the horses name is always unique,no two horses can be spelled the same.
So in *** Horse's Past Performace Data for last 10 races *** I take the field (45 in the data structure)
and place in the table to use it as a key then the table it self doesn' need one and add the Past P.
I do the same with*** Horse History Data *** when the info is workouts.

I don't expect to do all of this with one question.

In a MS article"Much ADO about text" They said ADO is the way to go. ADO is another problem for me.
LVL 46

Expert Comment

ID: 13423250
SAS is used to stand for Statistical Analysis System.  Like other corporations
they changed their name to just "SAS".

Most CSV text files aren't 1435 columns.  You are presenting a more
complicated problem to solve.

As long as the string fields don't contain any commas, you should be able
to use the SPLIT() function to easily parse each line into its separate data
in a variant array.  If they do contain commas, you would replace the commas
with some other character (usually > chr(127)) before you did the SPLIT.
Then you would have to apply another character replacement back to a comma.

Then you would add the indivicual array locations to different tables' fields.
This is where the mapping to your table structure comes in to play.  Also,
you could reduce the storage requirements and speed the imporation process
by NOT importing fields you won't use.

ADO is just an database connection/manipulation method.  MSAccess originally
used DAO as its programmatic database connection/manipulation method.

Author Comment

ID: 13426435
OK The array route is the only way.

I tried to use arrays when I first started and was hopelessly lost.
I did it with vb.net and could split ok, all the arrays cam out as strings so I had to remove the "" from the numbers.

Each row went from 1 to 1435 my array and took what I needed. Can I take smaller chunks out ?

The complex part is that Past P starts at 256 ends with 1145 but there is only 89-90 cols

If I use arrays can I use MS Access?

Yes or no's are OK You've already earned 500 pts.

If yes will continue to part 2 otherwise point me in the right direction with how best to phrase the next question ,you
seem to understand my problem.
LVL 46

Expert Comment

ID: 13428526
Actually, here's a better way to parse the data:

Dim intFilenum as Integer
Dim intField as Integer
Dim varArray(0 to 1434) as Variant
intFilenum = Freefile
Open "filename" For Input As #intFilenum

Do Until EOF(#intFilenum)
  For intField = 0 to 1434
    Input #intFilenum, varArray(intField)

  'This is where you open your tables or invoke your queries to insert your data

Close #intFilenum

Doing it this way means that the text fields will be stripped of their quote delimiters
automatically by the Input statement.  I used a zero-origin array for two reasons...
1. it is what the SPLIT() function would produce
2. a recordset's fields collection is zero-based

I'm not sure I understand a couple of things:
<<Each row went from 1 to 1435 my array and took what I needed. Can I take smaller chunks out ?>>
Yes, but only after you've read the record.  Of what "smaller chunks" are you refering?

<<The complex part is that Past P starts at 256 ends with 1145 but there is only 89-90 cols>>
I thought there were 1435 fields.  
* What is this 1145 number?
* There should certainly be more than 89-90 columns.

Do you only see that many fields or are you looking at the definition?
For instance:
"For each of the last 10 races (most recent to furthest back):
 256- 265 Race Date"

There are 10 fields defined here.
LVL 46

Expert Comment

ID: 13428668
<<These are the tables as in the csv file
*** Today's Horse/Trainer/Jockey/Owner ***
*** Horse History Data ***
*** Current Horse Stats ***
*** Horse's Past Performace Data for last 10 races ***
1264 o 1435 is a table also>>

1. but only certain sections have repeating data.  Horse History Data does not

2. in the "Current Horse Stats" section, Workouts repeat

3. every time you download this, you will be overwriting the prior download's

4. there are different sections within the 1264-1435 range that repeat.

Author Comment

ID: 13433103
ThanK you for taking for taking so much intrest with my problem.
In *** Horse's Past Performace Data for last 10 races ***( with out spicific field or array numbers)

Taking 10 array[0] array[2] etc this 10 would be a col in a table, the next 10 would be the next col etc.
In effect I would have a table of 89 fileds with 10 rows,but it would take 89x10 items to fill that table.

Now from array{0] to array [50] lets say this would be a row and the next line would be the same
and  all the fields would be the same type. so I would have a table of 51 fields and rows of as many horses
that are entered in the race,say 70.

>'This is where you open your tables or invoke your queries to insert your data<
Could you how what the query would look like?
If I had a horse(array[44] and wanted to know when the horse raced array[801]to array[810]

Author Comment

ID: 13433564
>Do Until EOF(#intFilenum)< is in red letters when I put the parse in Access.
Did I put the code in the wrong place?(I put in a button click)
LVL 46

Expert Comment

ID: 13435385
it should be EOF(intFilenum)

You make each distinct piece of data a column and then have multiple rows
to accomodate/represent instances of that data.
For instance:
Date of Workout
Time of Workout            
Track of Workout          
Distance of Workout        
Track Condition of Workout
Description of Workout    
Main/Inner track indicator
# of Works that day/distance          
"Rank" of the work among other works that day/dist

would be columns.  There would be 12 rows
LVL 46

Accepted Solution

aikimark earned 2000 total points
ID: 13435472
<<Could you how what the query would look like?>>

After you get your tables built in MSAccess, use the query builder to create an
Append query to a table.  Specify [parmColname] as the source/value of the
column's value.  In your program, you use either an ADO.Command or
DAO.Querydef to invoke the query with its Execute method.  
The parameters of the command/querydef object are made equal to the data
in your array.

You can also use a recordset object to add the data.  The recordset would point
to a table.  You would invoke the AddNew method and then set the fields equal
to the values in your array.  Then invoke the Update method.

Author Comment

ID: 13435497
I'm felling real guilty right now for all the time and effort you have done.
I will accept and continue with another question with a link.
I have many more questions.
Your abitity to pry out the info from me(I have no idea of the right terms most of the time)is outstanding.
thank you

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question