Importing Data from text file??

[calihiker]

Hi, I have a text file that contains MySQL statements for creating a table and inserting values into it. I am trying to figure out what the easiest way to import all the data into a access table. Is there an easy way to change the syntax to be able to use this code in Access?

Here is the code for the table and the data insert into statements...

CREATE TABLE shop_cart (
sessionid varchar(100) NOT NULL,
date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
cart text NOT NULL,
name varchar(100),
sname varchar(100),
address1 varchar(100),
saddress1 varchar(100),
address2 varchar(100),
saddress2 varchar(100),
city varchar(100),
scity varchar(100),
state varchar(100),
sstate varchar(100),
postalcode varchar(100),
spostalcode varchar(100),
country varchar(100),
scountry varchar(100),
phone varchar(100),
sphone varchar(100),
fax varchar(100),
sfax varchar(100),
email varchar(100),
comments text,
status varchar(40) NOT NULL,
KEY sessionid (sessionid)
);

#
# Dumping data for table 'shop_cart'
#

INSERT INTO shop_cart VALUES( '1765986', '2001-07-09 15:55:46', '1;35;TurboCAD v7 Professional Version;399.00;1;33;TurboCAD v7 Standard Version;99.00;', '', '', '', '', '', '', '', '', 'WY', 'WY', '', '', 'US', 'US', '', '', '', '', '', '', 'complete');
INSERT INTO shop_cart VALUES( '7956868', '2001-12-04 03:31:46', '1;121;SolidMech Upgrade (3.3)<br>(CD Version);50.00;0;', 'HENRY J KOOISTRA', 'HENRY J KOOISTRA', '8815 GLEN LN', '8815 GLEN LN', '', '', 'TULSA', 'TULSA', 'OK', 'OK', '74131', '74131', 'US', 'US', '(918) 270-9767', '(918) 270-9767', '(918) 270-9330', '(918) 270-9330', 'hank.k@lmco.com', '', 'complete');
INSERT INTO shop_cart VALUES( '4381350', '2002-01-11 06:41:11', '1;121;SolidMech Upgrade (3.3)<br>(CD Version);50.00;0;', 'Arthur Keigler', 'Arthur Keigler', '20 Pine Plain Road', '20 Pine Plain Road', '', '', 'Wellesley', 'Wellesley', 'MA', 'MA', '02481', '02481', 'US', 'US', '781 910 5760', '781 910 5760', '781 239 9745', '781 239 9745', 'akeigler@mediaone.net', '', 'complete');

Any help would be greatly appreciated, I tried figuring out how to use the code, but I couldn't. Now I am stuck with text-to-columns the data in excel and that alone causes alot of problems because the delimiter character, the comma, is in some of the string/fields and is seperating alot of the fields that need to kept together???

Thanks,
Andres

 

[SkipVought]

Hi,
cut and paste into a VBA module in Access and use to

1. create the table (or you can do it manually)

2. Use the INSERT SQL to load the table in a loop

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[calihiker]

Here is a portion of what I did...even though a couple lines worked ok, the number of lines I pasted into the module created an error saying the procedure was too large... Can you describe the code involved in order to loop through the lines of code? Right now it is in excel...

Thanks,
Andre


Sub Load_ShopCart()
DoCmd.SetWarnings False
DoCmd.RunSQL &quot; INSERT INTO shop_cart VALUES( '1905614', '2003-09-16 05:50:05', '1;13;regular;cd;', 'Mark Thrasher', NULL, '2085 S 600 W', NULL, '', NULL, 'Angola', NULL, 'IN', NULL, '46703', NULL, 'US', NULL, '(260) 475-5550', NULL, '(260) 475-1001', NULL, 'tweld1@msn.com', '', 'pending');&quot;
DoCmd.RunSQL &quot; INSERT INTO shop_cart VALUES( '9220639', '2003-09-16 11:10:22', '2;31;regular;dl;', 'allan teta', NULL, '444 south', NULL, '', NULL, 'solana beach', NULL, 'CA', NULL, '92075', NULL, 'US', NULL, '858-793-9091x205', NULL, '858-793-9162', NULL, 'allan@teamaja.com', '', 'pending');&quot;
DoCmd.SetWarnings True
End Sub

 

[SkipVought]

Unfortunately, I am no Access VBA guru. You migh want to post this in an Access FOrmu

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi Andre,

I took your create statement and it only needed a couple of minor changes to work in access.

It didn't like date as a column name - i changed it to ddate
It didn't like the DEFAULT date - I removed it
It didn't like the syntax for the key - I used constraint sessionid unique (sessionid)

Other than that it worked. I then took the first two of your inserts and ran them and it all looked good to me.

Here's what I ran:

Dim sql As String
sql = "CREATE TABLE shop_cart ("
sql = sql & "sessionid varchar(100) NOT NULL,"
sql = sql & "ddate datetime  NOT NULL,"
sql = sql & "   cart text NOT NULL,"
sql = sql & "   name varchar(100),"
sql = sql & "   sname varchar(100),"
sql = sql & "   address1 varchar(100),"
sql = sql & "   saddress1 varchar(100),"
sql = sql & "   address2 varchar(100),"
sql = sql & "   saddress2 varchar(100),"
sql = sql & "   city varchar(100),"
sql = sql & "   scity varchar(100),"
sql = sql & "   state varchar(100),"
sql = sql & "   sstate varchar(100),"
sql = sql & "   postalcode varchar(100),"
sql = sql & "   spostalcode varchar(100),"
sql = sql & "   country varchar(100),"
sql = sql & "   scountry varchar(100),"
sql = sql & "   phone varchar(100),"
sql = sql & "   sphone varchar(100),"
sql = sql & "   fax varchar(100),"
sql = sql & "   sfax varchar(100),"
sql = sql & "   email varchar(100),"
sql = sql & "   comments text,"
sql = sql & "   status varchar(40) NOT NULL,"
sql = sql & "   constraint  sessionid unique (sessionid)"
sql = sql & ");"

DoCmd.RunSQL sql
sql = "INSERT INTO shop_cart VALUES( '1765986', '2001-07-09 15:55:46', '1;35;TurboCAD v7 Professional Version;399.00;1;33;TurboCAD v7 Standard Version;99.00;', '', '', '', '', '', '', '', '', 'WY', 'WY', '', '', 'US', 'US', '', '', '', '', '', '', 'complete');"
DoCmd.RunSQL sql
sql = &quot;INSERT INTO shop_cart VALUES( '7956868', '2001-12-04 03:31:46', '1;121;SolidMech Upgrade (3.3)<br>(CD Version);50.00;0;', 'HENRY J KOOISTRA', 'HENRY J KOOISTRA', '8815 GLEN LN', '8815 GLEN LN', '', '', 'TULSA', 'TULSA', 'OK', 'OK', '74131', '74131', 'US', 'US', '(918) 270-9767', '(918) 270-9767', '(918) 270-9330', '(918) 270-9330', 'hank.k@lmco.com', '', 'complete');&quot;
DoCmd.RunSQL sql

Enjoy,
Tony