Solved SQL copies data with indent (1 Viewer)

[murray83]

Hi All

have exhasusted AI and google help so thought would actually ask some proper smart people ( me not included )

so i have the following SQL

INSERT INTO tblForCSV_main ( OrderNo, ProductCode, Qty )
SELECT Mid([RawData], InStr([RawData], "Booking Ref:") + 12, 12) AS BookingRef, Mid([RawData], InStr([RawData], "Pallets") + 21, 13) AS HSProductCode, Mid([RawData], InStr([RawData], "Pallets") + 68, 5) AS Qty
FROM TempRawData
WHERE TempRawData.RawData IS NOT NULL;

which for the most copies it fine apart from the OrderNo as it shows up like this in the tbl

am at a loss to why, but hope someone here will hold the key/secret

 

[plog]

Most likely this is because that's what's in the data. Pretty confident it has nothing to do with your SQL.

This probably gets fixed by putting a Trim() around each field of the SELECT.

 

[GPGeorge]

Hi All

have exhasusted AI and google help so thought would actually ask some proper smart people ( me not included )

so i have the following SQL

INSERT INTO tblForCSV_main ( OrderNo, ProductCode, Qty )
SELECT Mid([RawData], InStr([RawData], "Booking Ref:") + 12, 12) AS BookingRef, Mid([RawData], InStr([RawData], "Pallets") + 21, 13) AS HSProductCode, Mid([RawData], InStr([RawData], "Pallets") + 68, 5) AS Qty
FROM TempRawData
WHERE TempRawData.RawData IS NOT NULL;

which for the most copies it fine apart from the OrderNo as it shows up like this in the tbl

View attachment 120251

am at a loss to why, but hope someone here will hold the key/secret

Where is that "TempRawData" coming from? That would appear to be the source of the problem. It looks like a hidden line break character in that source text. I don't think it would be an indent, or tab character.

You can probably resolve the immediate problem by "sanitizing" the input from that TempRawData source.

For example, you could try using replace to remove the unwanted line break character. But a thorough solution might need to account for other such non-printing characters.

 

[Gasman]

Check the the length?

 

[jdraw]

murray83,
Crystal Long (strive4peace) provided a free routine to write the ascii values represented by characters in a string. If you use it to "display" the characters in your raw data, you can likely determine "strange characters causing your concern".
Let us know the result.

 

[murray83]

Cheers all. Not back in work till Friday so will give it all a look then

But cheers

 

[murray83]

ok have tried loading the page at work ( and looks like blocked booo )

have attached database and one pdf which is the raw data

so steps open the PDF copy all ( ctrl + a ) and then paste into subform on the frm_Import just using ctl + v ( no paste special required )

then click Import and you will see that the order number is indented

 

[Gasman]

You are picking up the CRLF of the previous line?

Might even be a tab in there?
Edit: No tab, that is my length display.

 

[murray83]

You are picking up the CRLF of the previos line?
View attachment 120271

ok so how do i try and clear that

is it as you said with (trim)

 

[Gasman]

No, trim removes spaces. In fact is is char(32) not CRLF.
Probably the easiest is to just run though the table afterwards and replace CHR(32) with nothing.
I would probably do it for all fields, as tomorrow, it could be another field.

 

[Gasman]

Or correct your calcs in the query? However if this has been working fine before, then tidying it up after would be safer?
This is just the select

 

[murray83]

how about tidying with

INSERT INTO tblForCSV_main (OrderNo, ProductCode, Qty)
SELECT
    Replace(Mid([RawData], InStr([RawData], "Booking Ref:") + 12, 12), ' ', '') AS BookingRef,
    Mid([RawData], InStr([RawData], "Pallets") + 21, 13) AS HSProductCode,
    Mid([RawData], InStr([RawData], "Pallets") + 68, 5) AS Qty
FROM
    TempRawData
WHERE
    TempRawData.RawData IS NOT NULL;

so does the insert and then cleans but again still seems to have that tab or what ever you said it was

 

[Gasman]

You would need to specify what character you are replacing. It is hidden after all.

INSERT INTO tblForCSV_main (OrderNo, ProductCode, Qty)
SELECT
    Replace(Mid([RawData], InStr([RawData], "Booking Ref:") + 12, 12), CHR(32), '') AS BookingRef,
    Mid([RawData], InStr([RawData], "Pallets") + 21, 13) AS HSProductCode,
    Mid([RawData], InStr([RawData], "Pallets") + 68, 5) AS Qty
FROM
    TempRawData
WHERE
    TempRawData.RawData IS NOT NULL;

However, what if it happens with the others? Might be safer to replace on those as well?

 

[Gasman]

Take the CRLF into account? That Chr(32) is elusive.

SELECT Replace(Mid([RawData],InStr([RawData],"Booking Ref:")+14,10),Chr(32),'') AS BookingRef, Asc(Mid([RawData],InStr([RawData],"Booking Ref:")+14,1)) AS Expr1, Mid([RawData],InStr([RawData],"Pallets")+21,13) AS HSProductCode, Mid([RawData],InStr([RawData],"Pallets")+68,5) AS Qty
FROM TempRawData
WHERE (((TempRawData.RawData) Is Not Null));

 

[murray83]

thanks for the reply which yes i didforget to say what to replace, but have some news

still has the gap should i not also put 13 for the CRLF ?

 

[Gasman]

See my last post.
Why not work out where the actual text starts?
This works?

SELECT Mid([RawData],InStr([RawData],"Booking Ref:")+14,10) AS BookingRef, Asc(Mid([RawData],InStr([RawData],"Booking Ref:")+14,1)) AS Expr1, Mid([RawData],InStr([RawData],"Pallets")+21,13) AS HSProductCode, Mid([RawData],InStr([RawData],"Pallets")+68,5) AS Qty
FROM TempRawData
WHERE (((TempRawData.RawData) Is Not Null));

 

[CJ_London]

nobody appears to be mentioning chr(11) which is a horizontal tab before the space (chr(32)- that also needs to be removed

Are you really using copy/paste to populate your table?

 

[Gasman]

nobody appears to be mentioning chr(11) which is a horizontal tab before the space (chr(32)- that also needs to be removed

Are you really using copy/paste to populate your table?

@CJ_London the 11 is the display of the length of the data

 

[murray83]

nobody appears to be mentioning chr(11) which is a horizontal tab before the space (chr(32)- that also needs to be removed

Are you really using copy/paste to populate your table?

yes as the data comes in a pdf and easiest way to get it in is copy and paste and then pick whats needed

and @Gasman sorry, yes you are correct it does work.

Many thanks all, closed ( for know )

 

[Gasman]

yes as the data comes in a pdf and easiest way to get it in is copy and paste and then pick whats needed

and @Gasman sorry, yes you are correct it does work.

Many thanks all, closed ( for know )

Well check it for a few runs, but it seems to pick up that Chr(32) as well as the CRLF.
However I think it was the CRLF causing your issue, the CHR(32) was found by accident.