CSV TO ACCESS (logged data)

userxyz · Jun 16, 2008

Hi,

In an other post/topic I build something to log several things. The thing is that I wanna do something with this data. I know a little MS Access. And I have CSV files.

I know if I save the CSV files as XLS files that I can make Tabels of them in Access, make query's...

I log the data on an MMC card.

Every minute the data is send to a serverlocation. Everyone in our department wants to be able to ask data in some sort of program/database.

If someone has an idea how to convert CSV to XLS automatically, this would be great... Or an other solution for my problem...

CharlesM · Jun 16, 2008

Every minute the data is send to a serverlocation. Everyone in our department wants to be able to ask data in some sort of program/database.

So do you want this to be automatic where it just happens or do you want to run the program to do it?

I have seen some good IT guys that would write some SQL to bring the data right in. I'm not that good myself but I am sure it can be done. If you wanted to do it manually then you could open the file in Excel save it then import into Access. To automate this a little you could save those steps into macros and just run them. My last suggestion would be to run a macro in Access that would read the file in. That would require some VBA work but could be done.

Could also do some search's
http://www.sobolsoft.com/accessimporttext/
http://www.microsoft.com/technet/scriptcenter/resources/qanda/feb07/hey0206.mspx

userxyz · Jun 16, 2008

I will start with writing something in MS access..., I'll save the CSV files to XLS files for my tests. WHen I have something that works good, then I'll try to implement something with VBA or with a macro, I will give some reply on this when I have some results,

regards,

G

CharlesM said:
So do you want this to be automatic where it just happens or do you want to run the program to do it?

I have seen some good IT guys that would write some SQL to bring the data right in. I'm not that good myself but I am sure it can be done. If you wanted to do it manually then you could open the file in Excel save it then import into Access. To automate this a little you could save those steps into macros and just run them. My last suggestion would be to run a macro in Access that would read the file in. That would require some VBA work but could be done.

Could also do some search's
http://www.sobolsoft.com/accessimporttext/
http://www.microsoft.com/technet/scriptcenter/resources/qanda/feb07/hey0206.mspx

surferb · Jun 16, 2008

You'll want to log data directly to a database. From there you can get the data to a .CSV file for use with Excel, but you'd probably be better off querying it directly. I'd recommend an SQL database over Access for a number of reasons. That's what OPC data logging tools like FactorySQL are designed to do.

Combo said:
Every minute the data is send to a serverlocation. Everyone in our department wants to be able to ask data in some sort of program/database.

... Or an other solution for my problem...

cjd1965 · Jun 17, 2008

Access can open a CSV file directly.

I would personally junk Access and use the free Microsoft SQL Express tools to do this. Are you going to keep the data in a flat file or are you going to create relational tables?

userxyz · Jun 17, 2008

Hey,

Microsoft SQL Express tools, never heard of it, I'm not an IT guy u know.

Well, the data should communicate, I mean, in some kind of form I wanna be able to ask how many products were produced, how many downtime there was, and what was the cause of the downtimes...

cjd1965 said:
Access can open a CSV file directly.

I would personally junk Access and use the free Microsoft SQL Express tools to do this. Are you going to keep the data in a flat file or are you going to create relational tables?

John Soltesz · Jun 17, 2008

It is best to store the data in a database. I think MS Access has issues with more than one person viewing the data at the same time. One of the other SQL type products is better suited.

We use a historian product (Intellution) to store our casting data that everyone has some sort of access to. The guys that analyze the data for quality compliance use Excel spreadsheets that I developed to extract the information from the database. I setup an array based on some preset criteria like temperatures, casting rate, pressures, mold level deviation and such. The user would simply enter a unique Heat Number and trim batch starting times if needed. The spreadsheet would fill with the requested data from the database and they would be able to view pre-made charts of this information. They could then transfer this information to a smaller spreadsheet for further use. There are a limited number of people that use this method as the product has a limited number of direct connections to the database depending on licenses purchased.

Another way the database is used is for viewing real-time and historical data for troubleshooting. Another product (Web Portal) will display pre-made charts the user can easily scroll through past data by using a web browser. The user can make/modify charts if needed. This works much better than Excel with the ability to zoom and scroll charts. Since this is web based it is easy for lots of users to have access to the data, but this is also license limited.

CSV TO ACCESS (logged data)

userxyz

Member

CharlesM

Member

userxyz

Member

surferb

Lifetime Supporting Member

cjd1965

Lifetime Supporting Member

userxyz

Member

John Soltesz

Member

Similar Topics