Method to Import CSV file information into Access Database Table

T

Tom Scott

New Member
Joined
May 6, 2013
Messages
6
I have these test files generated every day. The CSV file layout is the same every time, what changes is the J count. I can't figure out how to put the right data into the right field and keep adding until all the J values are accounted for.
I have this going into excel but want it in a database so it can be distrubited.
any help with this problem. Thank You



4A1
0x401F71F8302240
706070602.13000010TECH TOM0xF48
255255NOT SPECIFIEDNOT SPECIFIED 5/06/2013 1:03 PM8.07E+08193-723J2
J173632.268000
J275672.269000
J391222.265000
J468922.266000
J565512.274000
J665032.271000
J766352.297000
J878412.295000
J985692.243000
J1064632.246000
J1161732.292000
J1265262.297000
J1361352.303000
J1473442.245000
J1565862.295000
J1670662.282000
J1760472.282000
J1876722.28000
J1976362.273000
J2062792.284000
J2175582.261000
J2258142.261000
J2367402.278000
J2469312.263000

<colgroup><col span="14"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
xenou

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
Hi,
This might get you started. Given your data, this will read the text file, only the lines starting with "J". Here, we hold the values of each field in an array as we read each line (in practice, you would need to do something with them - either append to a table or transform to a clean file that can be uploaded, or what have you).

Code: 
[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR] [COLOR="SeaGreen"]'Scripting.FileSystemObject[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ts [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]  [COLOR="SeaGreen"]'TextStream[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] arr
[COLOR="SeaGreen"]'//path to text file - edit as needed[/COLOR]
[COLOR="Navy"]Const[/COLOR] fPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "C:\myTemp\jTest.csv"

    [COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR="Navy"]Set[/COLOR] ts = FSO.OpenTextFile(fPath, ForReading)
    [COLOR="Navy"]With[/COLOR] ts
        [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] [COLOR="Navy"]Not[/COLOR] .AtEndOfStream
            s = .ReadLine
            [COLOR="Navy"]If[/COLOR] Left(s, 1) = "J" [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "-- RECORD"
                arr = Split(s, ",")
                [COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] UBound(arr)
                    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "Field" & Format(i, "00") & ": " & arr(i)
                [COLOR="Navy"]Next[/COLOR] i
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]Loop[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    ts.Close
    [COLOR="Navy"]Set[/COLOR] ts = [COLOR="Navy"]Nothing[/COLOR]
    [COLOR="Navy"]Set[/COLOR] FSO = [COLOR="Navy"]Nothing[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


This is the text file I used for my test:
<a href="http://northernocean.net/etc/mrexcel/20130508_test_file.zip">Sample File (zipped)</a>
sha256 checksum: 4a9904d45b896218ab5b0d5365a3a731

Sample result (output to immediate window):
-- RECORD
Field00: J1
Field01: 7363
Field02: 2.268
Field03: 0
Field04: 0
Field05: 0
Field06:
Field07:
Field08:
Field09:
Field10:
Field11:
Field12:
Field13:
-- RECORD
Field00: J2
Field01: 7567
Field02: 2.269
Field03: 0
Field04: 0
Field05: 0
Field06:
Field07:
Field08:
Field09:
Field10:
Field11:
Field12:
Field13:
 
T

Tom Scott

New Member
Joined
May 6, 2013
Messages
6
Thank You ...
Next question, how do I import to named fields?
Example: the J values go into a table named "Jar", "Mhos", "Voltage"
the other data in file will need to go into other named fields. I am new at access,
and Thank You for the help. I may need someone else to code this for me
 
xenou

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
Hi,
Post your table structure (table names and field names). If possible give some sample data -- what data from the text files goes where. Your explanation above is confusing, because you say you have a table named "Jar", "Mhos", "Voltage" and a table cannot have three names! For writing scripts, exact information is required.

ξ


Note: I'm away on a trip the rest of this week but post more info about your tables and fields and how the data in the text file maps to the tables/fields in the database and someone else can probably help you work it out.
 
Last edited:
T

Tom Scott

New Member
Joined
May 6, 2013
Messages
6

ADVERTISEMENT

Thank You Sir,
I have provided links to several files you requested
What I am trying to do is import the csv data into a table
I am not sure of the best method and if i should use 2 tables for the imported data
1 for the general information linked to a jar table where the actual information for the test would be

Thank You again for taking the time to assist me with this.

Tom

https://dl.dropboxusercontent.com/u/23593649/Database2.accdb
https://dl.dropboxusercontent.com/u/23593649/UPSCF32.csv
https://dl.dropboxusercontent.com/u/23593649/UPSCF57.CSV
 
xenou

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
Hi,
I generally don't download files.
ξ
 
T

Tom Scott

New Member
Joined
May 6, 2013
Messages
6

ADVERTISEMENT

Test file below



CHANDLER
0x401F70F060
706070602.13000015TOM
261C & D / CONT'DUSER DEFINED 6 3/26/2013 8:01 AM1107480078192-330043F2
J17112.208000
J26742.208000
J36822.209000
J47352.188000
J56762.202000
J66642.207000
J76232.219000
J86242.196000
J96462.211000
J106212.221000
J116552.211000
J126592.202000
J136322.214000
J147252.202000
J156842.171000
J166622.214000
J176302.224000
J186552.221000
J19101422.215000
J206382.211000
J216642.208000
J227002.208000
J236642.208000
J246602.221000
J2590882.208000
J266592.214000
J277032.208000
J286922.221000
J296902.225000
J307212.221000
J317042.227000
J327202.239000
J337112.227000
J347192.227000
J356982.221000
J367102.236000
J376882.232000
J386892.233000
J396982.257000
J407232.251000
J417212.312000
J427302.258000
J436842.318000
J4402.227000
J456762.337000
J466602.324000
J476482.335000
J486952.338000
J497081.889000
J506882.33000
J517242.22000
J523852.094000
J537112.335000
J547182.349000
J556672.349000
J5621212.091000
J576882.361000
J586722.147000
J596742.149000
J606722.145000

<colgroup><col span="6"><col><col><col span="4"></colgroup><tbody>
</tbody>


TESTS TABLE is as follows

| TEST_ID |STRING | TEMP | UM | JAR COUNT | 70 | 60 | LV_WARN | TECHNICIAN | JARS_OVER1 | JARS_OVER2 | MFG_NAME | MODEL | TEST_DATE | TEST_TIME | TESTER_ID | TESTER_CODE |

JAR TABLE is as follows

| TEST ID || JAR_NUMBER | MHOS | DC_VOLTS | AC_R_VOLTS | AC_R_CURRENT | SP_GRAVITY |


THE TESTS table is linked to the ID field in JARS table ....
 
xenou

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
Hi,
Okay, so I see you have a file and two tables. What data from the file is supposed to go in which table?
ξ
 
T

Tom Scott

New Member
Joined
May 6, 2013
Messages
6
I hope this helps

Thank You for your help

TESTS TABLEID FIELD TO LINK TEST TABLE TO JAR TABLE
CVS LOCATION TABLE
A1STRING
B2TEMP
C2UNIT_TEMP
D2BASE_VALUE
E2JAR_COUNT
E3N_VALUE
F3L_VALUE
J3R_VALUE
K3T_VALUE
L3TECHNICIAN
M3TST_CODE
A4C_VALUE
B4L_TEMP
C4MFG
D4MODEL
E4 DATE
F4TIME
G4TESTER_SN
H4TST_SN
I4TST_CNT
JAR TABLEID FIELD TO LINK TO TEST ID (to match multi jar info
ID FIELD LINKED TO ID ON TEST TABLE
A6 - TO ALL W/ VALUES JAR
B6 - MHOS
C6VOLTAGE
D6RIPPLE_V
E6BASE
F6RIPPLE_I
G6SP_GRAVITY

<colgroup><col><col></colgroup><tbody>
</tbody>
 
You must log in or register to reply here.

Watch MrExcel Video

Learn Excel from MrExcel - Create A Bell Curve in Excel - Podcast #1663

Forum statistics

Threads
1,122,222
Messages
5,594,904
Members
413,952
Latest member
JGer

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top