Loading data into a single from a number of text files

Hi all,

How do I load data from a number of text files into a single mysql
table? My situation is as follows;

I have employee timesheets in Microsoft Excel 2003. I want to load
these into mysql for analysis. I see no direct way of connecting
Microsoft Excel 2003 to mysql. So, I plan to save the individual
timesheets as tab delimited text files. I want to load these into
mysql. For this, I will use the mysqlimport utility. However, the
mysqlimport utility does not allow me to specify the name of the
table where I want the data loaded. Yes, the load data statement from
within mysql will allow me to specify the table name but how do I
then give it multiple file names? This will be a weekly activity and
the number of time sheets will grow as the number of employees
increase so hard coding any value such as the file names is not an
option.

Yes, the best solution in my situation is probably a web based system
but at this point my employer is unwilling to allocate any money for
this.
Note:
I am running mysql on Windows XP.
Pranav


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Loading data into a single from a number of text files

On 24/08/2005, Pranav Lal wrote:

> How do I load data from a number of text files into a single mysql
> table? My situation is as follows;
>
> I have employee timesheets in Microsoft Excel 2003. I want to load
> these into mysql for analysis. I see no direct way of connecting
> Microsoft Excel 2003 to mysql. So, I plan to save the individual
> timesheets as tab delimited text files. I want to load these into
> mysql. For this, I will use the mysqlimport utility. However, the
> mysqlimport utility does not allow me to specify the name of the
> table where I want the data loaded.

The table into which mysqlimport puts the data is determined by the
filename of the text file
(see http://dev.mysql.com/doc/mysql/en/mysqlimport.html)

Can't you catenate all your csv files together and feed the (correctly
named) resulting file to mysqlimport?

--
felix

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Loading data into a single from a number of text files

On 24 Aug 2005 15:34:59 -0000, Felix Geerinckx
wrote:
> On 24/08/2005, Pranav Lal wrote:
>=20
> > How do I load data from a number of text files into a single mysql
> > table? My situation is as follows;
> >
> > I have employee timesheets in Microsoft Excel 2003. I want to load
> > these into mysql for analysis. I see no direct way of connecting
> > Microsoft Excel 2003 to mysql. So, I plan to save the individual
> > timesheets as tab delimited text files. I want to load these into
> > mysql. For this, I will use the mysqlimport utility. However, the
> > mysqlimport utility does not allow me to specify the name of the
> > table where I want the data loaded.
>=20
> The table into which mysqlimport puts the data is determined by the
> filename of the text file
> (see http://dev.mysql.com/doc/mysql/en/mysqlimport.html)
>=20
> Can't you catenate all your csv files together and feed the (correctly
> named) resulting file to mysqlimport?

That's a good solution, if that doesn't work, you can always build a
batch to do that, you can use cat and redirect to concatenate all
files within a directory into one, then rename the file as the table
name and import it, this way you wouldn't mess with your files
(because you would only "cat" them) and could build a daily big file
with the content of all the others.

Good luck,

--=20
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Loading data into a single from a number of text files

On 8/24/05, Pranav Lal wrote:
> Daniel,
> > build a
> batch to do that, you can use cat and redirect to concatenate all
> files within a directory into one, then rename the file as the table
> name and import it, this way you wouldn't mess with your files
> (because you would only "cat" them) and could build a daily big file
> with the content of all the others.
> PL] I am on Windows XP. I understand that cat is a unix command? I
> understand that the equivalent command on Windows is type?
>=20
> Pranav
>=20
>=20

Geesh, I'm getting banged with all this OSs switching here at the job,
yeah, yeah, its "type", didn't realized... Blame me if you use Linux
and Win and never did an "ls" at the DOS prompt :)

Sorry,

--=20
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Loading data into a single from a number of text files

--=_alternative 004E782985257068_=
Content-Type: text/plain; charset="US-ASCII"

FWIW, the DOS command you would use to create one file by concatenating
several files would be the COPY command.

This is from a Win2K command prompt but I can remember using the
concatenation form as far back as DOS 2.06 (on a PC Jr.)

C:\>copy /?
Copies one or more files to another location.

COPY [/V] [/N] [/Y | /-Y] [/Z] [/A | /B ] source [/A | /B]
[+ source [/A | /B] [+ ...]] [destination [/A | /B]]

source Specifies the file or files to be copied.
/A Indicates an ASCII text file.
/B Indicates a binary file.
destination Specifies the directory and/or filename for the new
file(s).
/V Verifies that new files are written correctly.
/N Uses short filename, if available, when copying a file with
a
non-8dot3 name.
/Y Suppresses prompting to confirm you want to overwrite an
existing destination file.
/-Y Causes prompting to confirm you want to overwrite an
existing destination file.
/Z Copies networked files in restartable mode.

The switch /Y may be preset in the COPYCMD environment variable.
This may be overridden with /-Y on the command line. Default is
to prompt on overwrites unless COPY command is being executed from
within a batch script.

To append files, specify a single file for destination, but multiple files
for source (using wildcards or file1+file2+file3 format).


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Daniel da Veiga wrote on 08/25/2005 11:09:41 AM:

> On 8/24/05, Pranav Lal wrote:
> > Daniel,
> > > > build a
> > batch to do that, you can use cat and redirect to concatenate all
> > files within a directory into one, then rename the file as the table
> > name and import it, this way you wouldn't mess with your files
> > (because you would only "cat" them) and could build a daily big file
> > with the content of all the others.
> > PL] I am on Windows XP. I understand that cat is a unix command? I
> > understand that the equivalent command on Windows is type?
> >
> > Pranav
> >
> >
>
> Geesh, I'm getting banged with all this OSs switching here at the job,
> yeah, yeah, its "type", didn't realized... Blame me if you use Linux
> and Win and never did an "ls" at the DOS prompt :)
>
> Sorry,
>
> --
> Daniel da Veiga
> Computer Operator - RS - Brazil
> -----BEGIN GEEK CODE BLOCK-----
> Version: 3.1
> GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
> PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
> ------END GEEK CODE BLOCK------
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 004E782985257068_=--

Re: Loading data into a single from a number of text files

There's always a better way to do things. (I'll be using this syntax
on my next batch script, I hate myself when I don't read the help)
Thanks Shawn.

On 8/25/05, SGreen@unimin.com wrote:
> =20
> FWIW, the DOS command you would use to create one file by concatenating
> several files would be the COPY command.=20
> =20
> This is from a Win2K command prompt but I can remember using the
> concatenation form as far back as DOS 2.06 (on a PC Jr.)=20
> =20
> C:\>copy /?=20
> Copies one or more files to another location.=20
> =20
> COPY [/V] [/N] [/Y | /-Y] [/Z] [/A | /B ] source [/A | /B]=20
> [+ source [/A | /B] [+ ...]] [destination [/A | /B]]=20
> =20
> source Specifies the file or files to be copied.=20
> /A Indicates an ASCII text file.=20
> /B Indicates a binary file.=20
> destination Specifies the directory and/or filename for the new file(s=
).=20
> /V Verifies that new files are written correctly.=20
> /N Uses short filename, if available, when copying a file wit=
h a
> non-8dot3 name.=20
> /Y Suppresses prompting to confirm you want to overwrite an=
=20
> existing destination file.=20
> /-Y Causes prompting to confirm you want to overwrite an=20
> existing destination file.=20
> /Z Copies networked files in restartable mode.=20
> =20
> The switch /Y may be preset in the COPYCMD environment variable.=20
> This may be overridden with /-Y on the command line. Default is=20
> to prompt on overwrites unless COPY command is being executed from=20
> within a batch script.=20
> =20
> To append files, specify a single file for destination, but multiple file=
s=20
> for source (using wildcards or file1+file2+file3 format).=20
> =20
> =20
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine=20
> =20
> =20
> Daniel da Veiga wrote on 08/25/2005 11:09:41 AM=
:
>=20
> =20
> > On 8/24/05, Pranav Lal wrote:
> > > Daniel,
> > > > > > build a
> > > batch to do that, you can use cat and redirect to concatenate all
> > > files within a directory into one, then rename the file as the table
> > > name and import it, this way you wouldn't mess with your files
> > > (because you would only "cat" them) and could build a daily big file
> > > with the content of all the others.
> > > PL] I am on Windows XP. I understand that cat is a unix command? I
> > > understand that the equivalent command on Windows is type?
> > >=20
> > > Pranav
> > >=20
> > >=20
> >=20
> > Geesh, I'm getting banged with all this OSs switching here at the job,
> > yeah, yeah, its "type", didn't realized... Blame me if you use Linux
> > and Win and never did an "ls" at the DOS prompt :)
> >=20
> > Sorry,
> >=20
> > --=20
> > Daniel da Veiga
> > Computer Operator - RS - Brazil
> > -----BEGIN GEEK CODE BLOCK-----
> > Version: 3.1
> > GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
> > PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
> > ------END GEEK CODE BLOCK------
> >=20
> > --=20
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: =20
> http://lists.mysql.com/win32?unsub=3Dsgreen@unimin.com
> >=20
> =20


--=20
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: Loading data into a single from a number of text files

I think something like=20

copy *.csv all.csv

will end up concatenating the files too.

To do jobs like this I usually end up writing some VB code to massage
the data through to MySQL. I have written Access procedures that open
Excel sheets via automation and send their data into linked tables in
MySQL. The sheets can be more complex than simple CSV tables too, as the
program logic can navigate around the sheets and pick up the data needed
and perform any filtering, reformatting etc. that might be required.

John Bonnett
Computer Scientist
Carl Zeiss Vision GmbH
Lonsdale, South Australia
Phone: +61 8 8392 8366
Fax: +61 8 8392 8400

-----Original Message-----
From: SGreen@unimin.com [mailto:SGreen@unimin.com]=20
Sent: Thursday, 25 August 2005 11:43 PM
To: Daniel da Veiga
Cc: MySQL Win32 List
Subject: Re: Loading data into a single from a number of text files

FWIW, the DOS command you would use to create one file by concatenating=20
several files would be the COPY command.

This is from a Win2K command prompt but I can remember using the=20
concatenation form as far back as DOS 2.06 (on a PC Jr.)

C:\>copy /?
Copies one or more files to another location.

COPY [/V] [/N] [/Y | /-Y] [/Z] [/A | /B ] source [/A | /B]
[+ source [/A | /B] [+ ...]] [destination [/A | /B]]

source Specifies the file or files to be copied.
/A Indicates an ASCII text file.
/B Indicates a binary file.
destination Specifies the directory and/or filename for the new=20
file(s).
/V Verifies that new files are written correctly.
/N Uses short filename, if available, when copying a file
with=20
a
non-8dot3 name.
/Y Suppresses prompting to confirm you want to overwrite an
existing destination file.
/-Y Causes prompting to confirm you want to overwrite an
existing destination file.
/Z Copies networked files in restartable mode.

The switch /Y may be preset in the COPYCMD environment variable.
This may be overridden with /-Y on the command line. Default is
to prompt on overwrites unless COPY command is being executed from
within a batch script.

To append files, specify a single file for destination, but multiple
files
for source (using wildcards or file1+file2+file3 format).


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Daniel da Veiga wrote on 08/25/2005 11:09:41
AM:

> On 8/24/05, Pranav Lal wrote:
> > Daniel,
> > > > build a
> > batch to do that, you can use cat and redirect to concatenate all
> > files within a directory into one, then rename the file as the table
> > name and import it, this way you wouldn't mess with your files
> > (because you would only "cat" them) and could build a daily big file
> > with the content of all the others.
> > PL] I am on Windows XP. I understand that cat is a unix command? I
> > understand that the equivalent command on Windows is type?
> >=20
> > Pranav
> >=20
> >=20
>=20
> Geesh, I'm getting banged with all this OSs switching here at the job,
> yeah, yeah, its "type", didn't realized... Blame me if you use Linux
> and Win and never did an "ls" at the DOS prompt :)
>=20
> Sorry,
>=20
> --=20
> Daniel da Veiga
> Computer Operator - RS - Brazil
> -----BEGIN GEEK CODE BLOCK-----
> Version: 3.1
> GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
> PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
> ------END GEEK CODE BLOCK------
>=20
> --=20
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/win32?unsub=3Dsgreen@unimin.com
>=20

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org