Text File Edit Before Import 3

[air1access]

I have a text file with an "=" sign on each line...
I need to remove this "=" sign before importing into an Access D/base- for each line...

My text file is actually a .csv file with headers on the 1st line for field names...

Not really sure how to go about this....
Any suggestions...?

 

[1DMF]

What code do you have?

How are you reading the file?

Why must it be edited before import and not processed after import?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Andrzejek]

Can you ask for the version of the file without "=" sign on each line?
Do you have other "=" signs in the file that you would like to keep?
Is the "=" sign always in the same location? (I.e. as the first character)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[air1access]

This file is a .csv file type, and has field names and the "=" is always in the same field.

Here is some code that I use in another function to edit a text file.
I just don't know how to get it to look for the "=" sign and remove it...

Open "D:\UPSDATA\ScheduledWork.txt" For Input As #1
Open strImportFile For Output As #2
Do Until EOF(1)
Line Input #1, strLine
If Left(strLine, 2) = "E " _
Or Left(strLine, 2) = "S " Then
strType = strLine
Print #2, strType
Line Input #1, strType
Print #2, strType
End If
Loop
Close #1
Close #2

Here is a sample of the .csv file data:

Contract Number,Presentation Language,Presentation Language other,Event Type,Event Name,Event Date,Event Time,Brokerage Firm/Agency,Facility Type,Facility Type other,Agent National Producer Number,Agent Name,Venue Name,Venue Phone,Venue Address 1,Venue Address 2,Venue City,Venue State,Venue Zip,Event Contact,Event Contact Phone
"H2649","English",,"Formal","9060505-HMO-TUCAZ-Sales Seminar (SS)","10/20/2015","10:00 AM","bbbb","Recreational/Community Center",,"2877607"," Melissa McKinney","Northwest YMCA","520-333-9001","7770 N SHANNON RD",,"TUCSON","AZ",="85741"," Jennifer Mazur","602-333-1766",
"H2649","English",,"Formal","9060506-HMO-TUCAZ-Sales Seminar (SS)","11/12/2015","10:00 AM","bbbb","Recreational/Community Center",,"2877607"," Melissa McKinney","Northwest YMCA","520-333-9001","7770 N SHANNON RD",,"TUCSON","AZ",="85741"," Jennifer Mazur","602-333-1766",
"H2649","English",,"Formal","9060507-HMO-PHOAZ-Sales Seminar (SS)","12/6/2015","2:00 PM","bbbb","Doctor’s Office",,"2590187"," Glenn Berkley","Iora Primary Care - Greenway","602-333-5575","3202 E Greenway",,"PHOENIX","AZ",="85032"," Jennifer Mazur","602-760-1766",

 

[dhookom]

Have you tried linking to the CSV file and then appending to your final table while removing the "="?

Duane
Hook'D on Access
MS Access MVP

 

[air1access]

I have tried that...

If you open the .csv file in Excel - the column that has the "=" shows it like its a function/formula or something in that cell...
The data is a zip code...


I created a spec to link - it either takes out the numbers behind the "=" (when set to a text data type) or I get the !NUM error thing when I change it to a number data type..

 

[MajP]

I imported into access without problem, then cleaned it with simple update query. Is that feasible?t

 

[SkipVought]

If you open the .csv file in Excel - the column that has the "=" shows it like its a function/formula or something in that cell...
The data is a zip code...

That does not seem to be what you ought to see in Excel. What happens in Excel, if you were to SELECT that column and change the Number Format to GENERAL?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[air1access]

Its set to General already....

The value shows as ="33460" in Excel in the formula bar or what ever.
But in the cell itself - it appears as 33460.

 

[SkipVought]

It appears that a person entered ="33460" but WHY? Perhaps from ignorance of a proper method.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MajP]

Public Sub CleanData()
  Const tblName = "Data"
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Set rs = CurrentDb.OpenRecordset(tblName, dbOpenDynaset)
  Do While Not rs.EOF
     For Each fld In rs.Fields
       rs.Edit
         If Not IsNull(fld.Value) And fld.Type = dbText Then
            fld.Value = Replace(fld.Value, """", "")
            fld.Value = Replace(fld.Value, "=", "")
         End If
       rs.Update
     Next fld
    rs.MoveNext
  Loop
End Sub

I imported and ran the above to remove the quotes and = sign. All the fields have quotes.

 

[Andrzejek]

If you want to get rid of ALL "=" signs in your file, you may use your code this way:

Open "D:\UPSDATA\ScheduledWork.txt" For Input As #1
Open strImportFile For Output As #2
Do Until EOF(1)
  Line Input #1, strLine
  strLine = Replace(strLine, "=", "")
  Print #2, strLine
Loop
Close #1
Close #2

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[air1access]

Thank you Andrzejek..!!
That did exactly what I need.
I had to modify it some what. Now Its skipping writing the very first line - which is the header names....
How can I get it to not skip the 1st line, write it out as is, and then proceed to process the rest of the lines..?

Do Until EOF(1)
Line Input #1, strLine

If Left(strLine, 1) = "O" Then
strLine = Replace(strLine, "=", "")
ElseIf Left(strLine, 1) = "N" Then
strLine = Replace(strLine, """""=", "")
Print #2, strLine
End If
Loop
Close #1
Close #2

End Sub

 

[Andrzejek]

1. Please use TGML tags to show your code.
2. Move your line [tt]Print #2, strLine [/tt]outside your [tt]If[/tt] statement[tt]
If Left(strLine, 1) = "O" Then[/tt]
you never write to #2 text file when this is true. Probably the very first (missing) line.
3. You can also do this:

Do Until EOF(1)
    Line Input #1, strLine

    Select Ccase Left(strLine, 1)
        Case "O"
            strLine = Replace(strLine, "=", "")
        Case "N"
            strLine = Replace(strLine, """""=", "")
    End Select

    Print #2, strLine
Loop
Close #1
Close #2

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

If you have some additional " (double quotes) before the =, using multiple " signs is confusing to me. I never know how many of them I need to place in order to get the right number I need.
I would rather do:
[tt]
strLine = Replace(strLine, Char(34) & Char(34) & "=", "")[/tt]

Which replaces [blue]"[/blue] and [blue]"[/blue] and [blue]=[/blue] with an empty string.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[air1access]

I have the TGML box "checked"...
I guess I don't know how to use it...
How can I learn..?

Thanks again..!
That did it..!!

 

[dhookom]

Air1access,
Using the TGML tags is about like formatting text using any word processing software. Select the text you want to format and click the icon such as the scroll which creates formatted code. Always click the Preview button prior to the Submit Post.

I pasted some of your answer below. Select the three lines and click the bulleted list icon from the drop down under the Smileys.
[ul]
[li]I have the TGML box "checked"...[/li]
[li]I guess I don't know how to use it...[/li]
[li]How can I learn..?[/li]
[/ul]

Duane
Hook'D on Access
MS Access MVP