TXT file to MDB, CSV

[Ninad]

Hi all,

I have some music songs in .txt format. The file names are 1.txt,
2.txt, 3.txt, etc. The fortmat of the .txt files are as follows:
------------Format starts------------
%
\startsong
\stitle{Song Title}%
\film{Film Name}%
\year{Year}%
\starring{Star Cast}%
\singer{Singers}%
\music{Musician}%
\lyrics{Lyricist}%
%
% Contributor:
% Transliterator:
% Credits:
% Editor:
%
\printtitle
#indian
%
song line 1
song line 2
song line 3
song line 4

song line 5
song line 6
%
#endindian
\endsong
%
------------Format ends------------

Can someone help in:
(1) Opening the first file
(2) Each line of the .txt file to be converted as a different field in
a new file NEWFILE
(3) Open the next file and repeat point (2)
(4) The subsequent files should all be appended to the same NEWFILE

Will appreciate any help rendered. This effort is to catalog the
collection as a database for personal use and not meant for me to make
any commercial gains.

Regards,

Ninad.

[miTcheLL]

Not knowing your experience level, you could put this sub on a button:

Private Sub Import()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strLine As String

Set db = CurrentDb

strFileName = "1.txt"
Open strFileName For Input As #1

Set rs = db.OpenRecordset("tblSongs", dbOpenDynaset)
rs.AddNew

Line Input #1, strLine
rs!startsong = startsong

Line Input #1, strLine
rs!stitle = strLine

.....continue for each variable

rs.Update
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Close #1
End Sub

"Ninad" <ni***********@hotmail.com> wrote in message
news:2a**************************@posting.google.c om...

Hi all,

I have some music songs in .txt format. The file names are 1.txt,
2.txt, 3.txt, etc. The fortmat of the .txt files are as follows:
------------Format starts------------
%
\startsong
\stitle{Song Title}%
\film{Film Name}%
\year{Year}%
\starring{Star Cast}%
\singer{Singers}%
\music{Musician}%
\lyrics{Lyricist}%
%
% Contributor:
% Transliterator:
% Credits:
% Editor:
%
\printtitle
#indian
%
song line 1
song line 2
song line 3
song line 4

song line 5
song line 6
%
#endindian
\endsong
%
------------Format ends------------

Can someone help in:
(1) Opening the first file
(2) Each line of the .txt file to be converted as a different field in
a new file NEWFILE
(3) Open the next file and repeat point (2)
(4) The subsequent files should all be appended to the same NEWFILE

Will appreciate any help rendered. This effort is to catalog the
collection as a database for personal use and not meant for me to make
any commercial gains.

Regards,

Ninad.

[Ninad]

Hi,

Not very good at programming, but tried to incorporate the code. I get
the error message at

Dim db As DAO.Database
Dim rs As DAO.Recordset as User-defined type not defined

Just a few other queries,

(1) Should I place this code in the module and call this funtion from
a button ? or
(2) insert this code in the On Click event of the button.

I'll try to work around this in the mean time. But thanks for taking
the time to help.

Regards,

Ninad.

"miTcheLL" <mitchell@"no_spam".topsideinc.com> wrote in message news:<1096807294.5Ox3uJjD7Pt5KhznEYkeLQ@teranews>. .. Not knowing your experience level, you could put this sub on a button:

Private Sub Import()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strLine As String

Set db = CurrentDb

strFileName = "1.txt"
Open strFileName For Input As #1

----------message snipped

[Tom van Stiphout]

On 3 Oct 2004 21:01:07 -0700, ni***********@hotmail.com (Ninad) wrote:

Make sure you have a reference (code window / tools / references) to
Microsoft DAO <highest version>.
You can probably remove the reference to ADO that's already there.

1 or 2: either one would work. If you intend to use the code from this
one form only, I would put it in the form.

-Tom.

Hi,

Not very good at programming, but tried to incorporate the code. I get
the error message at

Dim db As DAO.Database
Dim rs As DAO.Recordset

as User-defined type not defined

Just a few other queries,

(1) Should I place this code in the module and call this funtion from
a button ? or
(2) insert this code in the On Click event of the button.

I'll try to work around this in the mean time. But thanks for taking
the time to help.

Regards,

Ninad.

"miTcheLL" <mitchell@"no_spam".topsideinc.com> wrote in message news:<1096807294.5Ox3uJjD7Pt5KhznEYkeLQ@teranews>. ..

Not knowing your experience level, you could put this sub on a button:

Private Sub Import()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strLine As String

Set db = CurrentDb

strFileName = "1.txt"
Open strFileName For Input As #1

----------message snipped

[Ninad]

Hi,
Managed to get it to work. Only problem is there are 3250 such files
with file names as 1.txt, 2.txt ....... 3250.txt

When I use a for .. to loop and create a string such that all the
files can be done at one go, the file name gets created as below

The actual file name is "C:\PATH\1.txt" but instead a space gets
inserted before the 1 and gets generated as "C:\PATH\ 1.txt" The code
used is as follows

For varCounter = 1 to 3250
strFileName = chr(34) + "C:\PATH\" + str(varCounter) + chr(34)

Open strFileName............
..
..
..
..
..

Next

Anything wrong that needs mending.

Thanks for all the assistance.

Ninad.

-------------message snipped-------------

"miTcheLL" <mitchell@"no_spam".topsideinc.com> wrote in message news:<1096897397.zgzN4uvsyTs0vp8L75/QVQ@teranews>...

You need to check the reference to Microsoft DAO and move it up as far as it
will go in the references check list.
You would best place be behind a button on a form that performs the import.

miTch

[Trevor Best]

Ninad wrote:

Hi,
Managed to get it to work. Only problem is there are 3250 such files
with file names as 1.txt, 2.txt ....... 3250.txt

When I use a for .. to loop and create a string such that all the
files can be done at one go, the file name gets created as below

The actual file name is "C:\PATH\1.txt" but instead a space gets
inserted before the 1 and gets generated as "C:\PATH\ 1.txt" The code
used is as follows

For varCounter = 1 to 3250
strFileName = chr(34) + "C:\PATH\" + str(varCounter) + chr(34)

Open strFileName............
..
..
..
..
..

Next

Anything wrong that needs mending.

Use & instead of + for concatenation, there are subtle differences, you
should then also avoid the str() as a) it's not necessary when using "&"
and b) that's the cause of the space, it's the space for the sign, by
default a space instead of a + sign for positive. Not sure you want the
quotes either...

strFileName = "C:\PATH\" & varCounter

--
Pretentious? Moi?

[Pieter Linden]

ni***********@hotmail.com (Ninad) wrote in message news:<2a**************************@posting.google. com>...

Hi,
Managed to get it to work. Only problem is there are 3250 such files
with file names as 1.txt, 2.txt ....... 3250.txt

When I use a for .. to loop and create a string such that all the
files can be done at one go, the file name gets created as below

The actual file name is "C:\PATH\1.txt" but instead a space gets
inserted before the 1 and gets generated as "C:\PATH\ 1.txt" The code
used is as follows

For varCounter = 1 to 3250
strFileName = chr(34) + "C:\PATH\" + str(varCounter) + chr(34)

Open strFileName............

Ninand,
How about...

Public Sub CreateFiles(ByVal strPath As String, ByVal intStart As
Integer, ByVal intStop As Integer)
Const sFILE = "MYTEXT"
Dim intCounter As Integer

If Right$(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If

For intCounter = intStart To intStop
Debug.Print strPath & sFILE & intCounter & ".txt"
Next intCounter

End Sub

Modify to suit your needs, obviously.

HTH,
Pieter