Importing txt file into SQL workbench

Just put ";" after the statements.

Hold on!

[dbo] ???

Are you sure this is MySQL??? It looks like MS SQL Server to me!

I'm in mySQL workbench.

I've requested that this question be deleted for the following reason:

I don't think this will work.

Don't close the question yet. I'm working on it.

I had re-written it to the following.  Additionally, I've been trying to make the mysqldbimport script work with a csv file but haven't had any luck with that either.

DELIMITER '|'

CREATE PROCEDURE OnBoard_ImportScript_Postal_Place ()

BEGIN
-- Check if template table already exists and if it does then delete the table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[onboard_test].[Postal_Place]') AND type in (N'U'))
DROP TABLE onboard_test.Postal_Place
CREATE TABLE Postal_Place
(ZIP5 Varchar(5),
STATE2 Varchar (2),
STATE_ABBREV Varchar (2),
COUNTY5 Varchar (5),
COUNTY3 Varchar (3),
COUNTYNAME Varchar (25),
PLACENAME Varchar (28),
ZIP_TYPE Varchar (8),
MAILING_INDICATOR Varchar (1),
RESIDENTIAL_FLAG Varchar (1),
COMMUNITY_FLAG Varchar (1),
PREFD_PLACEKEY Varchar (6),
PREFD_PLACENAME Varchar (26),
ONBOARD_DATE Varchar (15))
-- Load data into table
BULK
INSERT Postal_Place_Test
FROM 'C:\Documents and Settings\Mike\Desktop\OnBoard\JC_zip-place-lkp_JUN13.txt'
WITH
(
FIRSTROW = 2, -- Removes the Header Row
FIELDTERMINATOR = '|',
ROWTERMINATOR = ''
)
END

what is your MySQL Server version?
can you provide a sample import file?

I found there is a mysqlimport tool provided with the installation pack.

I'm on version 5.2 and that simple import tool is what I've been trying to get working since my 1st post.  

I've been trying the following but getting the error below:
mysqldbimport.exe --server=root:db9988@localhost:3306 --import=both --bulk-insert  --new-storage-engine --format=csv  C:\Documents and Settings\Mike\Desktop\OnBoard\place-amenities.csv

Error:
C:\Program Files\MySQL\MySQL Workbench CE 5.2.40\utilities>mysqldbimport.exe --server=root:db9988@localhost:3306 --impor
t=both --bulk-insert  --new-storage-engine --format=csv  C:\Documents and Settings\Mike\Desktop\OnBoard\place-amenities.
csv
# Source on localhost: ... connected.
WARNING: New storage engine --format=csv is not supported on the server.
# Importing definitions and data from C:\Documents.
Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench CE 5.2.40\utilities\scripts\mysqldbimport.py", line 183, in <module>
    dbimport.import_file(server_values, file_name, options)
  File "C:\Program Files\MySQL\MySQL Workbench CE 5.2.40\python\mysql_libs.zip\mysql\utilities\command\dbimport.py", lin
e 838, in import_file
IOError: [Errno 2] No such file or directory: 'C:\\Documents'
Unable to start mysqldbimport, please verify that your installation is correct

Hang on.  I added quotes to my full path and it appears to be trying to import

That's wonderful.  Mine is still trying but the file is huge.  Do you think yours will work on a pipe delimited or do I need to convert to comma delimited?

I see that you used the load data infile.  My fear with that command is that MySQL saves the record after every entry and I have well over 1 million records to import.  Any ideas about that?

You say the file is huge... how huge?

here is the LOAD DATA INFILE documentation

I suppose you might need to use the LINES TERMINATED BY '|' option

Thanks for your help.  I have to run today but I'll be back on this Monday and give it a try.

If you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing. This option affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time.

Argenti,
Below is what I have as a function.  It does not error out but no data is coming in either.   Suggestions today?


drop table if exists `zip_place`;
 
create table `zip_place`
(ZIP5 Varchar (5),
STATE2 Varchar (2),
STATE_ABBREV Varchar (2),
COUNTY5 Varchar (5),
COUNTY3 Varchar (3),
COUNTYNAME Varchar (25),
PLACENAME Varchar (28),
ZIP_TYPE Varchar (8),
MAILING_INDICATOR Varchar (1),
RESIDENTIAL_FLAG Varchar (1),
COMMUNITY_FLAG Varchar (1),
PREFD_PLACEKEY Varchar (6),
PREFD_PLACENAME Varchar (26),
ONBOARD_DATE Varchar (15));

load data local infile 'C:\\Documents and Settings\\Mike\\Desktop\\OnBoard\\zip-place.csv' replace into table zip_place
fields terminated by ',' lines terminated by '\n'
ignore 1 lines;

I am not sure, but I suppose you should not run that as a stored function because it will execute on the server, while your csv file is located on local machine (see your local file path).
I would suggest
1. either save your drop+create+load data inline as s cript file then run your script from a client or
2. try uploading your csv file on the server then call your function with the appropriate file path.

Well, I am trying to import now as a *.txt & pipe delimited so I don't have to convert to a *.csv.  That works except I'm getting a funny symbol in the last column of data.

Second, is there a way to get it to prompt for a file name & path when I run it locally?

Thanks,

Use a variable for the path to your filename inside your script.
It won't prompt you for a filename, but each time you open the script in MySQL workbench you will see it and you'll know it has to be filled with the right path. You can use comments to indicate that, for just in case someone else is running your script.

The odd character in the last column must be from the line terminator. You said you are using pipe to terminate lines, so please put that in your load command " lines terminated by '|' " or " lines terminated by '|\n' ".

I terminated the line with /r/n and that seemed to work.

How would you do the variable?

-- Please fill in the full Path to the local import file
set @PathToImportFile =  'C:\\Documents and Settings\\Mike\\Desktop\\OnBoard\\zip-place.csv';

-- Check the output for import errors and/or warnings
load data local infile @PathToImportFile replace into table zip_place
fields terminated by ',' lines terminated by '\r\n'
ignore 1 lines;

Select allOpen in new window

Argenti,
I went to lunch so I'm just getting this.  Sorry for the delay.  Anyhow, thank you for all your help.

-- Please fill in the full Path of the file for import.
Set ImportFile = 'C:\\Documents and Settings\\Mike\\Desktop\\OnBoard\\zip-place.txt';

load data local infile ImportFile replace into table zip_place
fields terminated by '|'  lines terminated by '\r\n'
ignore 1 lines;

I copied your code but it doesn't recognize the variable Importfile.  do I need to define it?

You are right. Unfortunately it won't recognize the variable name inside the load data statement. Here is what they say in the documentation:

The file name must be given as a literal string.

I'm sorry, I thought it can be automated like prepared queries, but it seems it's not possible.

No problem.  Thanks anyway.

You're welcome.