CSV file first field check

Alan Kilborn

I would do a regular expression search mode Mark operation (Mark tab of Find window) and search for ^"\d{6}", and tick the Bookmark line option. Then I would invert the bookmarks using Search (menu) > Bookmark > Invert Bookmarked Lines. Then I’d jump between the bookmarks on lines that have problems.

There are other ways as well.

Alan Kilborn

Another way: This would produce a hit for the Find-result panel for every non-match of your criterion: ^(?!"\d{6}",).

It matches on the first character, but that’s probably sufficient for the need.

Wally Kempf

Thanks for the quick responses Alan. I tried both of your suggestions on a file that has the offending records in it, but neither suggestion found those particular lines. I don’t know if it is permissible or possible to send you a screen shot of what I’m talking about, to see if it sparks another idea.

Alan Kilborn

@Wally-Kempf

Best would be both a screenshot and a small section of data. Paste the data here, highlight it and then press the </> button right above the compose box.

Your data would then look like this (and most importantly wouldn’t be messed with by any “interpretation”:

I'm some data
I'm line 2
etc

Just a wild guess: Perhaps your double quotes are not the simple kind?
Like maybe they are U+2018, U+2019, U+201C or U+201D ; most likely some combination.

Wally Kempf

“040339”,“0006”,“013”,“Amherst at City View”,“”,“”,“”,“”,“”,“HOUSTON”,“TX”,“77060”,“17103 IMPERIAL VALLEY DR”,“227”,“75.00”,“8.000000”,“AI”,“gate code 2070”,“”,“1”,“”,“”,“”,“”,“”,“”,“”,“29.946850”,“-95.396846”,“1.00”,“”,“”,“174350”
“041217”,“0001”,“001”,“PALM BEACH ESTATES “,””,“”,“”,“”,“”,“HOUSTON”,“TX”,“77060”,“16818 CITY VIEW PL”,“227”,“76.00”,“8.000000”,“AI”,“GATE CODE 1454 SECTION 1
GATE CODE 1453 ACROSS THE STREET
ENTRANCE IS ON CITY VIEW OFF BENMAR”,“”,“1”,“”,“”,“”,“”,“”,“”,“”,“29.934018”,“-95.396125”,“1.00”,“”,“”,“174325”

I couldn’t paste the screen shot as it wouldn’t let me and I don’t see a compose box to do the rest of what you said. But the above is line 1 is good, line 2 is the start of the first bad record, meaning that there are carriage returns breaking the record of line 2 into lines 3 and line 4. I have to remove the carriage returns so that lines 3 and 4 move back up to line 2 as one complete record. I appreciate your help and hope I’m explaining this well enough. But it’s the “junk” record lines similar to lines 3 and 4 that I need to find since that line doesn’t contain “123456”,

Alan Kilborn

@Wally-Kempf said in CSV file first field check:

I couldn’t paste the screen shot

Really? You just copy your screenshot into the clipboard and hit ctrl+v while you are composing a post here. Super easy. But forget the screenshot, it is not that important.

I really can’t draw a conclusion from what you’ve posted, because you didn’t follow directions, so I don’t know if that is actually your data, or a version of your data that has been messed with by this website.

Paste your data here, select it here, and press this button (the one that I highlighted in yellow), right above the box you are composing in (called the “Compose” box):

If you don’t get a black-boxed version of your data, you haven’t done it right.

Wally Kempf

I’m sorry Alan. I won’t take up more of your time. I use Snag-IT to copy the part of the screen and when I right click within the reply box, the paste function is not active, although the copy is and using control v won’t paste it either. Also, as I’m replying, I don’t get any of the compose option that your screen shot shows (they don’t appear anywhere on my screen while replying in this forum). Apparently I don’t have “rights” to give you the complete information. Thank you for trying.

Alan Kilborn

@Wally-Kempf

Hmmm. I use Snag-it as well sometimes and have never had a problem pasting image data here. You can try this with your data, too, to format it properly (you may want to zoom in to see the bracketing characters better):

But really, I’ve never heard of so much difficulty creating posts with special stuff in them here.

I won’t take up more of your time.

Don’t worry about that. It is very unsatisfying to start something and not finish it, so…

Wally Kempf

"040339","0006","013","Amherst at City View","","","","","","HOUSTON","TX","77060","17103 IMPERIAL VALLEY DR","227","75.00","8.000000","AI","gate code 2070","","1","","","","","","","","29.946850","-95.396846","1.00","","","174350"
"041217","0001","001","PALM BEACH ESTATES ","","","","","","HOUSTON","TX","77060","16818 CITY VIEW PL","227","76.00","8.000000","AI","GATE CODE 1454  SECTION 1 
GATE CODE 1453  ACROSS THE STREET
ENTRANCE IS ON CITY VIEW OFF BENMAR","","1","","","","","","","","29.934018","-95.396125","1.00","","","174325"

Sorry- I figured out the issue. Now I’m using my local desktop. Previously I was using a remote server and apparently permissions are different. Thanks again.

Ekopalypse

@Wally-Kempf

@Alan-Kilborn 's regex seems to work for me

Sofistanpp

Hi @Wally-Kempf

Maybe I’m assuming too much, but if you want to get rid of those false line wraps, extend @Alan-Kilborn 's regex as follows:

Search:		(?s)(\R)(?!"\d{6}",)
Replace:	\x20

which produces this two-lines outcome:

"040339","0006","013","Amherst at City View","","","","","","HOUSTON","TX","77060","17103 IMPERIAL VALLEY DR","227","75.00","8.000000","AI","gate code 2070","","1","","","","","","","","29.946850","-95.396846","1.00","","","174350"
"041217","0001","001","PALM BEACH ESTATES ","","","","","","HOUSTON","TX","77060","16818 CITY VIEW PL","227","76.00","8.000000","AI","GATE CODE 1454  SECTION 1  GATE CODE 1453  ACROSS THE STREET ENTRANCE IS ON CITY VIEW OFF BENMAR","","1","","","","","","","","29.934018","-95.396125","1.00","","","174325"

Best Regards.