Visual Basic :: Convert More Than 255 Column Entries Of Database, Originally From Tab Delimited Text File

Import A Delimited Text File Into A Database
Hi all,

I am using the code below to Import a delimited text file into a database.By using that code I can import the same text file in the database.If the text file contains is already in the database stop the importation into the database.Can anyone help me in order to allow only one entry of a text in the database if the contains of the text file is already in the database

'Here is the code:

Code:
Option Explicit

Private Sub cmdImport_Click()
Dim delimiter As String
Dim wks As Workspace
Dim db As Database
Dim fnum As Integer
Dim text_line As String
Dim sql_statement As String
Dim pos As Integer
Dim num_records As Long

delimiter = cboDelimiter.Text
If Len(delimiter) = 0 Then
MsgBox "Please select a delimiter"
Exit Sub
End If

If delimiter = "<space>" Then delimiter = " "
If delimiter = "<tab>" Then delimiter = vbTab

' Open the text file.
fnum = FreeFile
On Error GoTo NoTextFile
Open txtTextFile.Text For Input As fnum

' Open the database.
On Error GoTo NoDatabase
Set wks = DBEngine.Workspaces(0)
Set db = wks.OpenDatabase(txtDatabaseFile.Text)
On Error GoTo 0

' Read the file and create records.
Do While Not EOF(fnum)
' Read a text line.
Line Input #fnum, text_line
If Len(text_line) > 0 Then
' Build an INSERT statement.
sql_statement = "INSERT INTO " & _
txtTable.Text & " VALUES ("
Do While Len(text_line) > 0
pos = InStr(text_line, delimiter)
If pos = 0 Then
' Add the rest of the line.
sql_statement = sql_statement & _
"'" & text_line & "', "
text_line = ""
Else
' Add the next field.
sql_statement = sql_statement & _
"'" & Left$(text_line, pos - 1) & _
"', "
text_line = Mid$(text_line, pos + Len(delimiter))
End If
Loop

' Remove the last comma.
sql_statement = Left$(sql_statement, Len(sql_statement) - 2) & ")"

' Insert the record.
On Error GoTo SQLError
db.Execute sql_statement
On Error GoTo 0
num_records = num_records + 1
End If
Loop

' Close the file and database.
Close fnum
db.Close
wks.Close
MsgBox "Inserted " & Format$(num_records) & " records"
Exit Sub

NoTextFile:
MsgBox "Error opening text file."
Exit Sub

NoDatabase:
MsgBox "Error opening database."
Close fnum
Exit Sub

SQLError:
MsgBox "Error executing SQL statement '" & _
sql_statement & "'"
Close fnum
db.Close
wks.Close
Exit Sub
End Sub
Private Sub Form_Load()
' Enter default file and database names.
txtTextFile.Text = App.Path & " estdata.txt"
txtDatabaseFile.Text = App.Path & " estdata.mdb"
End Sub

Import A Tab Delimited Text File Into A Sql Database
I have a log file which seperates the data by tabs. my question is, how do i import this text file into a sql databased that i've setup, using asp or a script?
thanks,
- gabe

Make Database From Delimited Text File
Is there a routine out there that will take a delimited text file and import it into an access database?

Or do I just have to loop through and create it myself?

Saving MS Access Database To A Tab-Delimited Text File
I am trying to write my microsoft access database out into tab-delimited text file.

However the following code only writes to a unicode text file, by using ADODB.Stream. I wandered if anybody could help me, by stateing how can I write to a tab-delimited text file instead.

Set mstream = New ADODB.Stream
mstream.Open

'write header
mstream.WriteText "Event Id" + vbTab
mstream.WriteText "Day" + vbTab
mstream.WriteText "Time" + vbTab

'write each record in timetable
Do While Not rsTimetable.EOF

mstream.WriteText rsTimetable("Event ID") + vbTab
mstream.WriteText rsTimetable("Day") + vbTab
mstream.WriteText rsTimetable("Time") + vbCr

rsTimetable.MoveNext
Loop

rsTimetable.Close
mstream.SaveToFile Filename, adSaveCreateOverWrite
mstream.Close

Any help would be much apreciated

Many Thanks

Jimbo

Read Delimited Text File And Insert Into Access Database
i need to read a delimited text file and display it in access database
this is my text file:

Type Date Time Source Category Event User Computer
Success Audit 7/14/2004 10:04:30 AM Security Logon/Logoff 538 SYSTEM L340C53
Success Audit 7/14/2004 10:04:30 AM Security Logon/Logoff 538 SYSTEM L340C53
Success Audit 7/14/2004 10:01:44 AM Security Logon/Logoff 538 SYSTEM L340C53
Success Audit 7/14/2004 10:01:34 AM Security Logon/Logoff 540 SYSTEM L340C53

May i know how to code it in visual basic 6.0 to display in the database table?

From Semicolon Delimited Text File To Microsoft Access Database Table
Hi,

I have a text file I receive daily with semicolons as delimiters. I was hoping to take certain data from this text file and shoot it into an Access database table (appending to existing data).

My goal is to create a table which historically tracks specific elements in this text file.

Any help or direction appreciated.

Search/Replace In Text File Based On Database Entries.
I'm writing an application to take a text file and run a find and replace function on the text based on some entries in a database. Say you have a text file that is comprised of thousands of lines that are formatted pretty similarly (comma delimited). I want it to read through the lines and every time it comes across the Product type of Equifax Infile, to change the Category to Developed Report. And for all Products that are undefined (else) the Category should be set to Developed Report. The categories and products are saved in a database that is editable by the user. The Product table contains a foreign key to link to the Category table so as to create a one-to-many relationship. As I am pretty new to the whole programming world, any help anyone could provide would be appreciated. I have sample code and text file if you would like. Please let me know if you have any ideas for this function. Thank you very much for your help.

Convert Common Delimited File To Right Justified Fixed Lenght File
Hi,
I have a common delimited file like this:

1000,000000012345,100,200020202
10,000002,2345,100,2000202020
1,000002,238945,100,2000202020

It needs to be converted to fixed length right justified file like this:
1000,000000012345, 11211,100, 200020202
10, 000002, 2345,100,2000202020
1, 000002,238945,100,2000202020

The problem I have is I need to find out the max. lenght for each column, then putting prefix empty string to make it right justifed fixed length column, and I don't want to read the file two times for this purpose since it is very big.

I am wondering what's the best to way to do this in VB6? any build in function like convert delimited file to fixed lenght file?

Any suggestion?

Thanks.
David

HOW TO CONVERT TAB DELIMITED FILE TO EXCEL FORMAT
Hi there,

I like to know how can i convert tab delimited text file to .XLS format.

Thanx

Reading Text File And Convert It To Database Table
In order to update a data base in access format I need to convert a text file in a VB application. How...
If some body have any solution please let me know
germanrc@tld.net

Delimited(;) Text Database && Asp
Hi,

I have a problem in my ASP page. It's regarding my textfile database.

Here is some code snippet:

Code:

Dim strConn, conADO, rstCoupon, strSQL

strConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strRoot & ";Extended Properties=""text;HDR=Yes;FMT=Delimited;"""

Set conADO = Server.CreateObject("ADODB.Connection")
Set rstCoupon = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM 200601.txt"

conADO.Open strConn

rstCoupon.CursorLocation = adUseClient
rstCoupon.Open strSQL, conADO, adOpenForwardOnly, adLockReadOnly, adCmdText

'Until here its ok ! Everything works.
'rstCoupon.RecordCount = 6 --> THAT'S WRONG ... I HAVE 7 LINES IN MY 200601.TXT FILE (SEE BELOW)

'But here it's getting wrong !!

Response.Write rstCoupon.Fields(0).Value ' This line work
Response.Write rstCoupon.Fields(1).Value ' This give me an error:

'Error:
'ADODB.Recordset error '800a0cc1'
'Item cannot be found in the collection corresponding to the requested name or ordinal.

Why is that happening?

This is the content of my 200601.txt file
Code:
"000009";"336802";"20050527";17;"20060331"
"000009";"336804";"20050527";34;"20060331"
"000010";"139251";"20050301";11;"20060331"
"000010";"139339";"20050301";12;"20060331"
"000010";"139340";"20050301";13;"20060331"
"000010";"139342";"20050301";11;"20060331"
"000010";"139343";"20050301";10;"20060331"

This is the content of the schema.ini file
Code:
[200601.txt]
ColNameHeader=False
Format=Delimited(;)
MaxScanRows=1
Col1=F1 Char Width 6
Col2=F2 Char Width 6
Col3=F3 Char Width 8
Col4=F4 Float
Col5=F5 Char Width 8

Why is the recordcount incorrect ?
Why can I get the first column in my database when there is 5 columns?

If I try in my SQL Statement to ORDER BY F2. It doesn't work, the ASP script becomes in error when I open the recordset. It doesn't know F2. Why ?

Thanks a lot for helping.

Edited by - dbelley_office on 9/6/2006 2:03:27 PM

Comma Delimited Text Database Error...
I made an address book that saves everything as a text db, compiled it without error, and it keeps poping up an error message every time I hit the next button.

ERROR MESSAGE: "Updating data in a linked table is not supported by this ISAM."

What the @#$% does that mean?

To Upload A Delimited File In Access Database
hi
I have an file which goes some thing like this
"BRK_HIGH 2' 8 7/16" Price- 349.00 SCREEN Bronze Manufacturer Client Name GALIGN No
ListPrice- 349.00 PrePrice- 349.00 INT_FIN Interior Prime MarkType Mark Label 0001 ParentPrePrice-nontaxable 0.00 SCRATCH Yes PrePriceAdj 349.00 PC_HIGH 26 TotalListPrice 349.00 TotalPrePrice-nontaxable 0.00 IPrice-nontaxable 0.00 ProjectCount AREA 5.777778 ProductCode 3226-1 xpos
À@
ProjectActive GRLCOLOR White PricingCode 3226 DIR L PKBuild
TotalPrice 349.00 IPrice 349.00 FLAG30 True SETUP Basic PrePrice 349.00 ProjectTaxStatus GRILLE Rectangular ID # 0001 GRLTYPE 3/4" Profiled Inner Grille ProjectTypes PC_WIDE 32 ProjectOAmounts Items Base Price: 3226-1 Aluminum Mesh Screen Charge Bronze Tint IG Tempered. White 3/4" Profiled Inner Grille Standard 3W2H Bronze Hardware Upcharge Interior Prime Charge

and this is just a part of the whole file , can some one tell me whats with the boxes and how do i get rid of it. I tried to get an ascii value , but looks like every other box has a diff value. can anyone pls help.

himangi.

Writing A Comma Delimited File From MS SQL Database
I need to perform a select against a Microsoft Sql table and write the recordset to a flat file delimited by commas. Does anyone know how to do this?

Create Comma-delimited File Using Access Database
I know how to use ADO to work with Access Databases, but I need to create a comma-delimited file using the information in the recordset and I am not too sure how to do that.

I am aware of the for Output command for sequential files, but it requires Write# var1,var2,var3...

Is that the best way if you have several fields in the recordset?

Thanks

mc

Tab Delimited Text File
Hi,

I am not sure what is happening here. I need to create a tab delimited text file and here is what I am doing:

Dim strTextLine As String

'open file.
Open "test.txt" For Output As #1

strTextLine = "!TRNS" & Chr(9) & "TRNSID" & & Chr(9) & "TRNSTYPE"
Write #1, strTextLine

'close file.
Close #1

When I open this in Excel, the tabs were not set. All the text is in the first cell and the tabs are seen as "squares" within the text.

What am I doing wrong here?

Thanks.

Delimited Text File
I am creating a tab delimited text file and inserting the following line:

Dim TransmitID As String

TransmitID = "000002"

Print #3, TransmitID; Chr(9); " "; Chr(9); Date; Chr(9); Date

This is fine but when I import this text file into excel it removes the leading zeros of the TransmitID. Anyone any ideas?

Also I have another line:

Print #3, rstMyTable!ck_tot; Chr(9); rstMyTable!Error

The field ck_tot is a Numeric field with 2 decimal places, but when it is imported into excel it is just a whole number.

Anyone any ideas?

Delimited Text File
Hi Everyone,

I'm trying to convert a Delimited text File to Excel. The text file uses the "|" (pipe) character as the delimiter.

What I need to know is how to detect whether a text file is delimited or not ?

I would really appreciate it, If someone could illustrate an answer with some code.

Thanks..

Delimited Text File To Dbf
How can I import a delimited text file and export to a dbase file through VB.
Thanks!

Tab Delimited Text File
I am creating a tab delimited text file and inserting the following line:

Dim TransmitID As String

TransmitID = "000002"

Print #3, TransmitID; Chr(9); " "; Chr(9); Date; Chr(9); Date

This is fine but when I import this text file into excel it removes the leading zeros of the TransmitID. I know excel does not recognise it as a string but I need to have the leading zeros.

Also I have another line:

Print #3, rstMyTable!ck_tot; Chr(9); rstMyTable!Error

The field ck_tot is a Numeric field with 2 decimal places, but when it is imported into excel it is just a whole number.

Anyone any ideas?

Delimited Text File
Hey! is anybody out there know how to open a delimited text file by using the ADO object?

Thanks,
Chris.C

Tab Delimited Text File
I have several text files in different formats from which I need to pull specific data out of and export that data into an Excel spreadsheet. Please HELP! I have attached a sample of one of the text files.

Converting A Comma Delimited File To An Inline Text File For Import
I'm looking for a way to convert a text file like this:-

INVNO,DATE,AMOUNT
1,06/10/2002,100
2,07/10/2002,150
3,08/10/2002,215
4,09/10/2002,100
5,10/10/2002,654
6,11/10/2002,12

into something like this:-

INVNO
DATE
AMOUNT
#MCBG
1
06/10/2002
100
#MCBG
2
07/10/2002
150 etc

#MCBG is a separator in the package i am using.

I've only just started this and kind of know what I am doing but its going to take a lot of playing around so any help would be grateful.

Thanks
Richard

Comma Delimited Text File
Hi All

I am trying to read a comma delimited text file from vb6, and have no idea where to start, if anyone could point me in the right direction i would really appreciate it. Ultimatly i would need to strip away all the commas then write the data into an access db. I'm not asking for code justb any advise or tutorials on how to get dtarted.

Many thanks in advance

Text Delimited File Import
Ok, New Problem.

I am currently tryign to convert my access 97 Applications into "real" Applications using Visual Basic 6.0.

The Problem I am having is:

In Access we import a text delimited File in.
Inside of this file there may be 70 - 150 rows. Within Each row there is 34 fields that I need to import in to fields into a table.

So How could I do this in VB?

thanks

Reading A Tab Delimited Text File
Hi,

How can I read & get the data in to 2D array from a tab delimited text file?

Thanks,
Chamila

Help Parsing A Non-delimited Text File
I have a text file that I need to export into excel or a comma delimited file. The file consists of a solid string of prices consisting of numbers and decimal points. The only way I can think to break it down is by finding the decimal point in the string advancing 2 digits for the cents(the price is not a fixed length throughout the file) parse it and keep repeating. Also the information represents columnar data,(i.e. there are 7 fields so after each 7th price a new line should be started to build the columns required. I'm sure this is quite simple but I've been away from programming too long for it to click. Any help is appreciated!

Parsing A Delimited Text File
I am trying to capture specific parts of a text file that is delimited with the "|" symbol and in lines that begins with "FT1".
ie. FT1 |A|B|C|This part is captured|D|E|F
The information will be in that segment every time but the number of lines there are will vary.

Any ideas?

Tab Delimited Text File And Arrays
I've seen many threads that talk about tab delimited text files, but none tells me what I need to know.

I have a tab-delimited text file that holds a person's name,email address, and phone number
I need that inputed into a multi-dimension array.
Example:

VB Code:
name(0,0)="Mine"name(0,1)="example@example.com"name(0,3)="555-1212"

what would be the easiest way to do this?

Creating A Text Delimited File
I just took a visual basic course and I am writing very simple visual basic programs. One of the programs that I am writing creates a text delimited file. The problem the text is being created delimited by commas. I needed it to be delimited by spaces for example I need

"Mortgage" "234" instead of
"Mortgage","234"

How do I instruct visual basic to delimit my file with spaces instead of commas?? Thanks for your help

Help Reading In Delimited Text File?
Hi all,

I have a text file that is delimited with a pipe | . What i want to do is read in the pipe separedt values into either a dbgrid or a listview control.
ANy ideas how i can accomplish this?

Thanks for any help!
later
b

Need Some Help Parsing A Delimited Text File
I need some help getting my program to open a delimited text file and placing the information in the correct text boxes.

i have the following in the txt files
†charles
Intro to Win2k adv serv§Basic Admin course 3 days
Intro to ISA Server§Basic ISA server Install and config course

†is used to seperate users records
then comes the course on a new line
and the § seperates the course from its description

i can get it to open the entire file for editing which is useless, i need to be able to send each peice, seperated by user to a different box, ie lists the users in a text box and when you click on a user it would update the crstxtbox and update the course and description. which need to be editable seperatly but saved together.

I also have two other small things.

I need to add in a new field for each record which is a small picture which is stored in a seperate directory. and when you open the record it open the image in a picture box, and if it doesn't have a picture listed in the record it leaves it blank or loads a default picture.

and since many of these courses are the same i need the 2nd text box seperated with a drop down in which you can select the course, but not be limited to selecting a known course and be able to enter a new course

I hope this makes some sense
If some of you nice guys out there could help me with this. Also if you post code for if you have a chance could you please also post comments in it to what each part does, otherwise I am just coping and pasteing, and I would like to learn wht I am putting in so I can do it on my own next time I have a question

thanks

KC

Space Delimited Text File
I am working on a subroutine to convert space-delimited files to semicolon-delimited files. Right now the file looks something like this:

Adam Smith 45 Cherry St. 789-0098
Susan Sarandon 123 123rd Avenue 555-5555
Walter Matthau 54 Hollywood Blvd. 123-4567

Right now I am using the code

Open CommonDialog1.Filename For Input as #File1
Line Input #File1, strText

Open CommonDialog2.Filename For Output as #File2
Print #File2, Texto

Obviously all this does is copy the file but unfortunately that is about the extent of my VB experience. I need a subroutine that will read the file, replace the white spaces between columns with a ";" and also return the column length (e.g. the number of spaces between the A of Adam Smith and the first number of his phone number).

any suggestions will be GREATLY appreciated!

Thanks,

Andrew

Text File Query Tab Delimited VB6
OK big problem here
I am kinda new to this so bare with me

I want to take a text file that is tab delimited (output of a pascal program). Then have it seperated into their specific columns in an access database (pre existing).

Example of text file

This is the header of the text file---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Significant Data Below this statement (Column Names then Information)

Position (Tab) Mileage (Tab) Exception
1 123 CW

End

This file can contain lots of these entries. I am hoping I can get this to work. I hate working with 2 previously created files.

I cant get the top paragraph off of it (i guess a disclaimer of sorts). So I would greatly like the help.

Thanks

Creating A Delimited Text File
It's me, the noob, again. My project is going well, but now I'm into the thick of it.

I need to create a delimited .txt file using information from an access database. I have been using DAO thus far.

I am having trouble finding any documentation that tells me how to pull an individual field from a record and write it to file. Any help/advice would be greatly appreciated (once again). thanks,

Jason

Writing A Delimited Text File
i'm trying to write text to an excel file which is delimited by tab, can anyone help?

i've tried this and it doesn't work

File8.Open Dialog1.FileName, _
fsModeAppend, fsModeOutput, fsAccessReadWrite, fsLockWrite
File8.LinePrint ("a" & " " & "test")
File8.Close

Delimited Text File Assistance
Consider a comma delimited text file:

"John Doe",999-999-9999,02-12-76,"Sales",125.05
"Jackie Doe",666-999-9999,02-12-62,"Repair",125.05
"Jane Doe",777-999-9999,07-22-92,"Sales",125.05
"Jake Doe",555-999-9999,05-12-82,"Service",125.05
"Joe Darrowe",333-999-9999,12-12-82,"Sales",125.05
"John Dorkman",222-999-9999,03-12-72,"Repair",125.05

I am trying to read through this type of file and read each line into
an array assigned for each field... (i.e: Name, SSN, DOB, Dept, Commision)
for each line. Then I want to display an individual report sheet for
each dept. So that when I call for the display of that report sheet it will
display only those persons in that dept.

Also I wanna be able to edit information in those fields and append it back
to the original text file after it has changed.

I am trying to find out how I would go about coding this.

Any help would be greatly appreciated...

Space Delimited Text File
I am working on a subroutine to convert space-delimited files to semicolon-delimited files. Right now the file looks something like this:

Adam Smith 45 Cherry St. 789-0098
Susan Sarandon 123 123rd Avenue 555-5555
Walter Matthau 54 Hollywood Blvd. 123-4567

Right now I am using the code

[vbcode]
Open CommonDialog1.Filename For Input as #File1
Line Input #File1, strText

Open CommonDialog2.Filename For Output as #File2
Print #File2, Texto

Obviously all this does is copy the file but unfortunately that is about the extent of my VB experience. I need a subroutine that will read the file, replace the white spaces between columns with a ";" and also return the column length (e.g. the number of spaces between the A of Adam Smith and the first number of his phone number).

any suggestions will be GREATLY appreciated!

Thanks,

Andrew

Tab Delimited Text File Text Parsing
I have a query that I wrote and ran in SQL Analyzer which produces three columns of information from three different tables. I set Analyzer up to dump the contents of this query to a text file rather than in its grid. I now have a tab delimited text file with the three columns of information.

What I need to do now is read that text file in using Vb, and dump the contents of column one into Richtextbox1, column two into Richtextbox2 and column three into Richtextbox3. How would I go about doing that?

Read Position Delimited Text File
hi gurus

I am new to this, so go esay on me.

I new to read a position delimited text file,so the first field is from 1-4 then the next field is from 5-5 and then next field 6-8, I need to read the whole line and extract information based on the position, when they start and end

eventually this information will go into excel but I guess I need to read this file first beforre I go to excel.

Thanks a bunch gurus

Read Delimited Text File Into An Array
I want to convert a delimited text file into an array

'################################
LineOfFile = 0
Open (FilePath) For Input As #1
While Not EOF(1)
Line Input #1, TempString
TempArray = Split(TempString)
For X = 0 To UBound(TempArray) - 1
MainArray(LineOfFile, X) = TempArray(X)
Next X
LineOfFile = LineOfFile + 1
Wend
Close #1
'################################

without having to read one line at a time?

I see posts for the Input$() function but no examples...

Can someone give me an example?

PS: Are there size limitations? Some of the text files have 30,000 lines.

Thanks

Delimited Text File To 2 Dimensional Array
Hi,

I have a text file that contains two columns seperated by a delimiter ;.
I would like to store the values into a two dimensional array, so that I can retrieve one value from the array and it's corresponding value from the same array for furthur processing. Can this be accomplished?

Thanks

Importing Data From A Tab Delimited Text File Using Vb?
Hi,

We have a requirement where we get various types of user database in excel or text files and we have to import it into our sql server database.

What we used to do till now is that read each n every record from excel file using a ado connection inserting / updating it into the users table. As it is too time consuming, we planned to import the excel or text file directly into sql server as a new table and then insertion or updation becomes easy.

Now for inserting bulk records we have the BULK INSERT query and which works very well. But for it we have to have a table in place and follow the same structure for the text file.

Is there any way that we can create a table on fly and then inserting all the records one by one into the new table. I am asking the same thing that MS SQL servers import tool does. It just asks the source excel file and then the new table name and automatically creates the table and inserts the row.

Thanks and regards

Hitendra

Parsing Comma Delimited Text File
I am trying to parse text file which has comma delimited fields that are enclosed by ".

"LastName", "First", "MI", "12345", "Soc.Sec.Num", "Some text with, commas inside the, field"

I was trying to use something like this:

Open frmDataInloader.txtFileName For Input As #1
Do Until EOF(1)
Line Input #1, Data
Data = Replace(Data, """", "")
mfield = Split(Data, ",")
DoEvents
Loop
Close #1

This is throwing off my field count when i come across fields with commas inside the field.

Time Sensitive, any help appreciated.