Dear mysql-ians,
I am using mysql 5.0 and I want to load a huge txt-file in my database.
My text file (file.txt) looks like:
col1 col2 col3 ... col200
col1 col2 col3 ... col200
....
col1 col2 col3 ... col200
I now want it to import in a table t1 with two columns (col_nr,
col_val) where col_nr is the number of column (e.g. 2 for col2, 46 for
col46) and col_val are the effective values in my txt-file at the
different columns. The problem is that col_nr is not in the "file.txt"
so I have to assign based on the field number.
I am looking at the LOAD DATA INFILE command, but in the help file I
did not find an answer to my question. I was hoping to do it with:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(col_val)
SET col_nr = "how do I do this";
Does anyone has any suggestions or tips to do it differently (with php
perhaps)?
Thanks in advance!
Stef 6 2019 st************* **@agr.kuleuven .ac.be wrote: Dear mysql-ians,
I am using mysql 5.0 and I want to load a huge txt-file in my database. My text file (file.txt) looks like:
col1 col2 col3 ... col200 col1 col2 col3 ... col200 ... col1 col2 col3 ... col200
I now want it to import in a table t1 with two columns (col_nr, col_val) where col_nr is the number of column (e.g. 2 for col2, 46 for col46) and col_val are the effective values in my txt-file at the different columns. The problem is that col_nr is not in the "file.txt" so I have to assign based on the field number.
I am looking at the LOAD DATA INFILE command, but in the help file I did not find an answer to my question. I was hoping to do it with:
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (col_val) SET col_nr = "how do I do this";
Does anyone has any suggestions or tips to do it differently (with php perhaps)?
Thanks in advance!
Stef
Each row in the text document has different values for the column number,
yes? So the table will have lots of rows with col_nr = 3 and different
values in col_var? Also how are the columns delimited in the text file?
Paul Lautman schreef: Each row in the text document has different values for the column number, yes? So the table will have lots of rows with col_nr = 3 and different values in col_var?
Indeed my table has 270 columns and the values for every field for
every row are different. Below you can find a small extract of my
file.txt (with only 3 columns and 8 rows).
38 61 1
35 64 1
35 64 1
41 59 0
39 61 0
42 58 0
28 72 0
36 64 0
Also how are the columns delimited in the text file?
My text-file is organised in a fixed length fomat for every field (as
you can see in the extract).
I also see that the SET command is only valid from MySQL 5.0.3 and I
just donwloaded 5.0.21 so I assume the SET will not work anyway. Do you
have other suggestions?
For your information: my text file contains al lot of info (270 fields
X 2M records).
Thanks in advance,
Stef
Just to clarify. As I understand it, you want to take the file and
rearrange it like this (I'm using your sample data):
COL_NR | VALUE
1 | 38
1 | 35
1 | 35
1 | 41
1 | 39
1 | 42
1 | 28
1 | 36
2 | 61
2 | etc, etc.
So maybe your table should have a 3rd column, or, more precisely a
'1st' column which will hold the key to the table - like this:
id | col_nr | value
1 | 1 | 38
2 | 1 | 35
3 | 1 | 35
4 | 1 | etc, etc.
As for getting the data into the table, I'm a bit stuck. I think that
the PHP solution might be the way to go - with a loop that counts to
270 for instance. st************* **@agr.kuleuven .ac.be wrote: Paul Lautman schreef:
Each row in the text document has different values for the column number, yes? So the table will have lots of rows with col_nr = 3 and different values in col_var?
Indeed my table has 270 columns and the values for every field for every row are different. Below you can find a small extract of my file.txt (with only 3 columns and 8 rows).
38 61 1 35 64 1 35 64 1 41 59 0 39 61 0 42 58 0 28 72 0 36 64 0
Also how are the columns delimited in the text file?
My text-file is organised in a fixed length fomat for every field (as you can see in the extract).
I also see that the SET command is only valid from MySQL 5.0.3 and I just donwloaded 5.0.21 so I assume the SET will not work anyway. Do you have other suggestions?
For your information: my text file contains al lot of info (270 fields X 2M records).
Thanks in advance, Stef
When I used to need to do this sort of things back when I worked for IBM, I
would always turn to PIPELINEs to change that file into one that would load
into the database (actually if putting into DB/2 I would use the PIPELINEs
DB/2 device driver to maipulate the data and feed it straight into the
database using INSERT statements created in the PIPELINE).
Datamystic's .com TextPipe product enables you to do this on a PC.
However, even without PIPELINEs, I would still be tempted to preprocess your
file using whatever came to hand (REXX, VB, VBA, PHP, PERL, ...) to make it
look like:
1,38
1,35
1,35
1,41
1,39
1,42
1,28
1,36
2,61
2,64
2,64
2,59
2,61
2,58
2,72
2,64
3,1
3,1
3,1
3,0
3,0
3,0
3,0
3,0
Which could then be loaded simply into the database. Is every field the same
length, or do they vary and is there always at least one space between the
fields, or do some run into each other? st************* **@agr.kuleuven .ac.be wrote: Dear mysql-ians,
I am using mysql 5.0 and I want to load a huge txt-file in my database. My text file (file.txt) looks like:
col1 col2 col3 ... col200 col1 col2 col3 ... col200 ... col1 col2 col3 ... col200
I now want it to import in a table t1 with two columns (col_nr, col_val) where col_nr is the number of column (e.g. 2 for col2, 46 for col46) and col_val are the effective values in my txt-file at the different columns. The problem is that col_nr is not in the "file.txt" so I have to assign based on the field number.
One solution would be to do it in two steps. Create a new table with
200 columns, load the data from the file, and then use SQL to copy the
values fom that table to the two-column destination table.
CREATE TABLE t1_wide( col1 CHAR(3), col2 CHAR(3), col3 CHAR(3), ...
col200 CHAR(3));
LOAD DATA INFILE INTO TABLE t1_wide FIELDS TERMINATED BY ' ';
INSERT INTO t1 SELECT 1, col1 FROM t1_wide;
INSERT INTO t1 SELECT 2, col2 FROM t1_wide;
INSERT INTO t1 SELECT 3, col3 FROM t1_wide;
....
INSERT INTO t1 SELECT 200, col200 FROM t1_wide;
Another solution would be to write a custom application in your favorite
programming language to parse the text file and execute the appropriate
INSERT statements to load the data.
Regards,
Bill K. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Pieter Van Waeyenberge |
last post by:
Hello
i have most recent php & mysql versions running on a linuxmachine
i want to use LOAD DATA LOCAL INFILE and pass it from php to mysql.
- the file i load is given with full path and the directory is readble
- On MySQL local-infile = ON
- From PHP i connect using client flag (128) to allow passing a file over
that connection: mysq_connect( $host, $usr, $pwd, false, 128)
|
by: Bruce A. Julseth |
last post by:
When I execute this SQL statement in my PHP code, I get an error
"File '.\Address.txt' not found (Errcode: 2)"
$File = addslashes(".\Address.txt");
$SQL = "Load Data InFile \"" . $File . "\" into table addresses";
$result = mysql_query($SQL) or die(mysql_error());
The file is located in the same directory as my .PHP file.
How do I generate a relative address for this file so that it can be found?
|
by: Otto |
last post by:
Hello to all
I have a problem how to write correctly a PHP string.
I did the following import with the help of PHPMyAdmin:
Nombre d'enregistrements insérés : 364 (traitement: 0.0553
sec.)
requête SQL:
LOAD DATA LOCAL INFILE '/tmp/phpVfO8r8' INTO TABLE `fncid`
|
by: hall |
last post by:
I have a problem with my design of a templatized class. I'm trying to
figure out how to load and save the data inside it, but can't. My class
looks like this
------------------------------------
template <class tType> class Foo {
private:
tType data;
|
by: Donald Tyler |
last post by:
Then the only way you can do it that I can think of is to write a PHP script
to do basically what PHPMyAdmin is trying to do but without the LOCAL in
there.
However to do that you would need to be able to place the PHP file on the
server, and I guess you probably can't do that either. Talk about catch
22...
The only other way I can think of is to install MySQL on a machine you
control, then import the data there using the method I...
| |
by: Jami Bradley |
last post by:
HI all,
For the past several months we have been using LOAD DATA LOCAL INFILE to bulk load tables within
Perl modules.
Recently, someone thought it would be a good idea to upgrade the Solaris machines - I'm thinking it
wasn't that good an idea :-(
We are now running MySQL 4.0.18 and the DBI version is 1.35. Now, our scripts are failing with the
dreaded "The used command is not allowed with this MySQL version" message. Here's what I...
|
by: Ray in HK |
last post by:
What are the differences between LOAD DATA INFILE and LOAD DATA LOCAL INFILE
?
I found some web hosting company do not allow using LOAD DATA INFILE but
allow LOAD DATA LOCAL INFILE. The reason is for the sake of security. What
does that mean ?
|
by: nsh |
last post by:
mailing.database.mysql, comp.lang.php
subject: does "LOAD DATA" EVER work?!? I've tried EVERYTHING!
version info:
my isp is running my web page on a linux box with php ver. 4.4.1
according to phpinfo, the "mysql api client is ver. 4.0.25" - I have no
idea how this relates, if at all, to the mysql engine's version.
background:
|
by: lanesbalik |
last post by:
hi all,
right now i'm trying to migrate from db2 running under linux to mysql v5.1.
i manage to export out the db2 structure & data into a del (ascii) file.
but when i try to load the data from the del file to mysql table, it generate an error.
below is the load data infile syntax i use =
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |