using REGEX() to parse a CSV file via I/O stream (1 Viewer)

[vba_php]

has anyone ever done this? I attempted to parse these CSV's with a simple method of finding a universal string that was common in all the files, which appears at the end of each one at the 2nd to last field. this is what I'm pointing to and using SPLIT() to gather the array elements, which come in as entire records in the CSV:

,ea,

so after running this code on files that are actually formatted/downloaded correctly:

Line Input #1, streamText
streamArray() = Split(streamText, ",ea,")

I get this collection in the array elements, which is the way it should be:

1/11/2019, Payment,60, 1, January
1/11/2019, Payment,12.49, 1, January
1/11/2019, Cedar Rapids,8.5, 1, January
1/11/2019, Cedar Rapids,5.99, 1, January
1/10/2019, Cedar Rapids,6.25, 1, January

there's a lot more fields that come in when the actual code runs. the data above is thrown into a second array I have written because the first one using SPLIT() brings in way too much data from the CSV record that is irrelevant. however, on one CSV, SPLIT() throws nothing to the 1st array even though the actual data in the offending file looks like this and has the exact string I'm looking for:

https://www.access-programmers.co.uk/forums/attachments/unparsable_file_csv-jpg.78575/?hash=16e2be69c0069f3f9c2ebfe52f37bb2d

this string is in every CSV file, so this really doesn't make any sense. furthermore, if I try to throw the CSV data into an array by using SPLIT() and specifying the parser as the carriage return vbcrlf, there is *more* than 1 file that doesn't give me anything in the accepting array. I did a presentation for a lady the other day and she complained to me that the Verizon software she was using was downloading invoice records outside the date range she was specifying. That's exactly the same type of problem that is occurring with these people I am referencing with this problem. They are the engineers at the company SQUARE, which is the mobile app payment method for smartphones. These files they are giving customers to download are not reading the same way everytime with respect to an I/O stream conducted in windows using VBA. So who is the culprit here? Windows technology, VBA or the square engineers? My guess would be the engineers at square because I've seen stuff like this happen countless times from large corporations....anyone got any insight on the issue? thanks! and to the subject of this thread, if I used REGEX(), would it make my life easier, and would it solve this problem?

 

[arnelgp]

have you tried the simple method of importing the csv using DoCmd.TransferText method.

 

[vba_php]

have you tried the simple method of importing the csv using DoCmd.TransferText method.

actually arne, I think I did. are you talking about the VBA method? if you are, DOCMD is only available in Access, not Excel. Isn't that right? It doesn't register for me in excel vba's intellisense drodown.

 

[vba_php]

arnelgp,

I looked up your suggestion here: https://www.google.com/search?q=ms+excel+vba+transfertext, and it looks like my suspicion is correct. It's only available in Access it seems like. Per this page: https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transfertext. I also tested it out in Excel and I saw nothing happen like I mentioned in my previous post:

Do you know something I don't? If there *is* actually a way to import text files via VBA in Excel that is not I/O in nature, I'm all ears! Because this process I'm using is obviously unreliable. thanks!

 

[moke123]

You didn't indicate you were using excel. There appear to be a lot of google hits on the subject

 

[vba_php]

You didn't indicate you were using excel. There appear to be a lot of google hits on the subject

oh my. I'm not sure I have an answer for that! I must have posted this in the wrong forum. My apologies to you all. This should be in the Excel forum. I just had so many complaints I guess I lost track of what program I was working with! I'll take a look at what you've posted, Moke. thanks. sorry about that, arnelgp!

 

[vba_php]

here's an update on this problem you guys. the employer called me today and said this:

thank you for the solution. everything works great, and I didn't encounter any errors when running the code like you told me I would

what I was complaining about in this thread, was the weirdness I was seeing from excel, even though there was nothing wrong with my automation. The error that happened, and is still happening for me, is this:

but my code is *supposed* to produce the custom error messages box I wrote when a file is found to be unparsable:

and the employer is using Excel 2007 and 2010 So you guys tell me....what is with this? There is almost no change, that I know of, between 2007, 2010 and 2016. This is exactly why I don't use office products much anymore. There's nothing wrong with them of course, but it's stuff like this that is completely unacceptable in terms of a large company offering products to small business people and are supposed to be paying attention and giving individual developers reliable tools to work with that don't contain anomalies like this. Or maybe I'm just an unlucky guy!