Importing Data + Module + strFileData is blank

Moxioron

Active Member
Joined
Mar 17, 2008
Messages
436
Office Version
  1. 2019
Hello all.

I am stumped. I have a module in Access that I am using to import data from a text file. Because the data I am importing has two lines per transaction, I am trying to bring both lines in.

This is what I have in VB:

If StrComp(Mid(strFileData, 61, 4), ".00 ", vbBinaryCompare) = 0 Then
strShortName = Mid(strFileData, 15, 26)
'End If

I had to put a space after .00 because the line above is also monetary, but has a - behind it. If I don't put a space behind the .00, Visual Basic is looking at both lines and pulls in data all ove the place.

When I put the space after the .00, nothing is being imported.

Any ideas or advice? Thank you for your help.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
It would really help if we could see a sample if the text file you are importing.

can you paste a 4 to 8 (2 -4 transaction pairs) sample lines?
 
Upvote 0
Here is one transaction (two lines):

02011 EDB09 000XXXXXXXXXXXX 70 80 22 641.27- 641.27-
MOUSE MICKEY R .00 641.27-


Line one is a piece of cake. line two, however, is what I am having iddues with. I want to import the Short Name (MOUSE MICKEY R) and want to use the .00 as a constant. However, if the amount line above ($641.27-) ends in .00, it obviously doesn't work well. So I thought by putting a space at the end of .00 that would do it, but no can do.

Thanks for your help.
 
Upvote 0
If the lines are really split into two lines per transaction and it isn't a word wrap situation then you can't use the normal method of import. You would need to read each row individually and import just that part (most likely using the Open FilePath For Input As #1 moethod of code and then using append queries built in SQL to append the values based on which line it is.
 
Upvote 0
Thank you for response.

I have used the normal method of input (as it was taught to me anyway) on several different occassions when the data was on multiple lines.

The difference is that I have always had a constant to use to identify the data I am trying to import. For this text file the second line doesn't really have any constants, with the exception of the .00.

I am not familiar with the other methods you mentioned, but I will give it a whirl. Thanks again.
 
Upvote 0
After seeing the data sample I totally agree with Bob.

I normally handle this by processing the file reading in the lines in pairs

One option would be to first read through the file and convert the data to be on a single line then you could import the data using the built in import utilities. If that sounds like a viable options I could put togeter some code for you.
 
Upvote 0
If there is a better way to import that would be great.

I have only been using Modules for about two years and the code I use is recycled code (change the constants) so I love learning from the experts.

Anything you can provide to put me in the right direction, would be greatly appreciated.

Thanks!
 
Upvote 0
Here is code to convert your file from 2 line transaction to 1 line transaction.

Code:
Public Sub convertfile2to1()


Dim FH As Integer
Dim FH2 As Integer

Dim strPath As String
Dim strPathOut As String
Dim strLineIn As String
Dim strLineOut As String

'input file path
strPath = "c:\mydata\trans2line.txt"

' output path for new file converted to 1 line transaction
strPathOut = "c:\mydata\trans1line.txt"

FH = FreeFile

Open strPath For Input As #FH

FH2 = FreeFile

Open strPathOut For Output As #FH2

Do
   ' get first line
   Line Input #FH, strLineIn
   strLineOut = strLineIn
   ' get second  line
   Line Input #FH, strLineIn
   strLineOut = strLineOut & " " & strLineIn
   
   
   Print #FH2, strLineOut
  
   
Loop Until EOF(FH)

Close #FH
Close #FH2


End Sub
 
Upvote 0
Yes sir. That worked pefectly. Thank you for your assistance. Greatlly appreciated.
 
Upvote 0

Forum statistics

Threads
1,224,527
Messages
6,179,328
Members
452,907
Latest member
Roland Deschain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top