importing excel data to database

[austin101]

hello i'm trying to make a databse that imports data from a excell csv file format wich i know how to do but i do not know how to make a macro or something like that would only take ceratin parts of the csv file in excel because i do not need everything in the file.
i have to do this everyday.
could someone help me out. i would give an attchment but i have to pay for it when i followed the link to post this thread.
can someone guid me how to solve me problem. this is for a bussiness database.

 

[NXMold]

There are dozens of methods to import from excel. You can connect to an excel file as if its another database and build a recordset or query (I can't help with the specifics of this method), or you can programaticaly open and manipulate excel, moving around in cells and all that. You can use the access import methods, transferspreadsheet, etc into a temp table and then use an update query to commit parts of that data into your main table.

Are you trying to pick data from a cell here, a cell there? Or are you importing bulk rows of data, but trying to filter it?

 

[austin101]

im picking from a cell here and a cell there

 

[austin101]

http://cid-2a3bf2dc86b27502.skydrive.live.com/embedrowdetail.aspx/.Public

here is the csv file and can someone make a database to go with it
and randomly pick data out and show me an example how to do it with some written code. thanks please help

 

[NXMold]

Hmm... some of this is out of my depth, but I would attack it this way. Presumably the layout is consistent, but you still don't want to reference specific cells since extra rows could be added. Therefore, you'll probably want to step through the file one value at a time looking for data. The file is formatted as a visual report, not so good for being machine readable.

As you step through values look for "Sales by Category", then branch into a loop where you look for "SANDWICH", "FRY", etc and save that information into your database as you want, exit the loop when you get to "Subtotal" or "Totals".

Now you could use VBA to open and control excel, but since its a nice simple CSV file it is probably better to just read it directly.

Open "\\networkpath\folder\comma.csv" For Input As #1
Do While Not EOF(1)
Input #1, colA, colB, colC, colD, colE, colF, colG colH, colI,
If colC = "Gross Sales" Then 'do something with colD data
Loop
Close #1

Of course it will take a little time to develop this code, I'm just throwing out an idea for a starting point. You'll probably need to open a recordset (or several) in your database in order to insert the data, or use sql.

 

[austin101]

thank you for this code you are the fisrt to try to help me with what i need.
i appreciate this alot, thank you.

 

[ZiggyS1]

is this just a single file you are working with, or are you importing a new CSV file everytime?

If it is a single file you should just link it and then manage the data in Access by appending what you need to the table(s) you need it in.

If you need automation, I have code I use that reads a directory and automatically imports the data.

In either case, I still treat the Import/Linked table as a temporary storage, and then use queries to extract what I want.

 

[austin101]

hey guys sorry it took so long to reply heres what i need.
i need to make something that will export these cells to a database. they will be listed below. i am importing a new csv file each time but the cells i need each time will need to go into a existing table that i need to create so i can keep importing the data into them.
make up a field for these items please
A1 to go into its own field.
A2 to go into its own field.
A3 to go into its own field.
A5 to go into its own field.
C7 to go into its own field.
C11 to go into its own field.
C19 to go into its own field.
C21 to go into its own field.
C23 to go into its own field.
C25 to go into its own field.
C29 to go into its own field.
C30 to go into its own field.
C30 to go into its own field.
C38 to go into its own field.
C39 to go into its own field.
C40 to go into its own field.
C44 to go into its own field.
C47 to go into its own field.
C48 to go into its own field.
C49 to go into its own field.
C51 to go into its own field.
C56 through C65 to go into one there own sperete fields
D56 through D65 to go into one there own sperete fields
E56 throguh E65 to go into one there own sperete fields
C69 to go into its own field.
D69 to go into its own field.
E69 to go into its own field.
all of these need to go into a new database and i need to do this to several sheets all the time is it possible please help.
the links to these items are below.







i will post the files in the next post.

 

[austin101]

heres the files that i need to work with\\\

http://cid-2a3bf2dc86b27502.skydriv...Public/attachments|_2009|_05|_22.zip?ccr=2169

 

[SkipVought]

Hi,

A1 to go into its own field.
A2 to go into its own field.
A3 to go into its own field.
A5 to go into its own field.
...

That's no proper mapping specification. What does that really mean? It is AMBIGUOUS!
[tt]
A1 is mapped to WHAT as WHAT?
A2 is mapped to WHAT as WHAT?
[/tt]
What about A4. Does it go anywhere?

Is ther any other data in column A?

What about column B data?

What about C1 thru C6?

So many UNANSWERED questions.

You need to develop a DETAILED specification, it would seem to me, in order to get some help.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ZiggyS1]

Hi Austin,

one more thing to clarify, will the file name always be the same? If it is you can link the Excel file to Access and then over write it with the new one each time.

I looked at your Excel file, where are you getting it from...it looks like a report pasted to Excel, it is only a CSV in that it was saved as that type...a CSV is separated by Comma's between columns?

The "CSV" as you call it is begging for an Excel Macro to clean it up into a better layout, mainly because I don't believe the ranges are constant... eg..is "C48 to go into its own field." always going to contain "TO GO" where C46 is the main heading "SalesOrderMode"

Provide more backgound on the Excel file, and how constistant the data in the cells is.

 

[MazeWorX]

Just opened the CSV file and in column 'A' there is no data. In your post above are you referencing the actual data or the description field for the data?

Also what does cell B3 represent? "5.3.46"

ZiggyS1 is right to ask is the file consistent every time?
Are the cell references the same on each file?
I’m assuming this report comes from your POS … can that system export the report in another format?

Just a side note the totals don’t need to be imported as they can be calculated from the data imported

Skip is also right to ask details, details, details.
When the data is imported into a table it would be nice to give the table fields names of relevance.

Provide as much detail as you can

MaZeWorX

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[Fretwalker]

Haven't read all of the posts (ADD?), so if this has been mentioned already , then consider it confirmation. I like to use "generics" where possible. If you're importing the same data, or any part of it, on a regular basis setting a link to the csv file (File, get external data, link table...) to one of the files renamed say "DailyData" will get you a link that can be viewed by all internal forms, queries, etc and all you have to do is change the name of the new csv file to "DailyData" to do that. With only a bit more programming you can get program the app to get the file, write it to the current location as "DailyData" replacing yesterdays's version and presto, everything is now up to date and ready for data processing.

That will work every time after you get it set up.

 

[austin101]

well thanks for trying to help me people but i just figured it out in excel
i made a three marcos the third one runs marco 1 and 2 and then after it sets itself up right then i can import it into a my data base
for all of you thanks for helping i found someof your comments were usefull

heres what i got i will list below but i will not show the marcos with it but iwill show the excel sheet after the marcos done there work.

http://cid-2a3bf2dc86b27502.skydrive.live.com/browse.aspx/.Public?uc=1&lc=1033