Space in file name when using DBConnect

[kpassaur]

Below is a portion of a script that accesses a csv file. It works correctly if there is no space in the file name. If there is a space it fails.

I though single quotes '%csvfile%' would fix it but it didn't. I have tried numerous combinations of quotes and double quotes etc with no sucess.

Any ideas?

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%csvpath%;Extended Properties='text;HDR=NO;FMT=Delimited;Option=1'
DBConnect>ConStr,dbH
Let>SQL=select * from %csvfile%

[Bob Hansen]

Where do you define csvfile?
Don't use " or ' around or inside %csvfile%

Have you tried:
Let>csvfile="File Name.ext"

[kpassaur]

Bob,

I just tried

Let>csvfile='%csvfile%'
and
Let>csvfile="%csvfile%"
Both with no success

[JRL]

Try converting your path\filename to DOS 8.3 format. Here's sample code

Code: Select all

Let>Path=C:\Documents and Settings\Dickl
LibFunc>Kernel32,GetShortPathNameA,GSPNres,Path,0,0
LibFunc>Kernel32,GetShortPathNameA,GSPNres2,Path,char,GSPNres
MDL>GSPNres2_2

[kpassaur]

JRL

I'm missing something here as the path works with spaces only the file name does not.

[JRL]

Its just a thought. If the issue is spaces in the file name, remove them. If you don't have control of the spaces in the file name, then convert the name to DOS 8.3 where there are no spaces.

[kpassaur]

Brilliant

I will copy it into a temp folder and then process it with no spaces.

Thank you

[kpassaur]

I don't get it as it works on some files and not on others. I can take a csv file test it and it works, copy the file to a different folder and it will not.

Same file, different path and it will not work. It always fails if there are spaces in the file name. I tried copying it to a folder where there are no spaces in the path or file name and it would work on one an not another.

It maybe useful to some if it can be fixed as what it does is take a csv file (or text file) and format it so that there are always " and commas as delimiters and separators. Some appliciaons require it for importing and some only require the " for a field with a comma.

Naturally it could easily be changed to use any separated the user wanted.

Anyway, if anyone has any ideas on how to make it stable it would be great.

// COMPILE_OPTS|C:\bob\format.exe||CONSOLE=0|INCLUDES=1|
Let>APP_TITLE=Format CSV
%TEMP_DIR%temp.txt
Dialog>dlgProgress
Caption=Progress
Width=484
Height=119
Top=192
Left=534
ProgressBar=progressbar,16,48,441,16,0
Label=Please Wait,200,16,true
EndDialog>dlgProgress


Let>comma=,
Input>fileandpath,Select an Input File to convert to "%comma%" delimited,
IfFileExists>%fileandpath%
Goto>startread
Else
MDL>No file selected will now exit
Goto>EOF
Endif

Label>startread


//Gets file name
Let>Pattern=([0-9a-z_-\s]+[\.][0-9a-z_-\s]{1,4})$
RegEx>%Pattern%,%fileandpath%,0,matches,num,0
Let>csvfile=matches_1
//Confirms Path

StringReplace>%fileandpath%,\%csvfile%,,csvpath


Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%csvpath%;Extended Properties='text;HDR=NO;FMT=Delimited;Option=1'
DBConnect>ConStr,dbH
Let>SQL=select * from %csvfile%
DBQuery>dbH,SQL,fields,num_recs,num_fields
DBClose>dbH
Let>loops=%num_fields%
If>%loops%=0
MDL>Error connecting to File
Exit>0
Endif

Let>rn=num_recs

Show>dlgProgress

Let>rec=0
Repeat>rec

Let>rec=rec+1
Let>z=1
Let>output="
Label>startreadline
Let>value=fields_%rec%_%z%
ConCat>%output%,%value%
ConCat>%output%,"
If>%loops%=%z%
Goto>finishline
Else
ConCat>%output%,%comma%
ConCat>%output%,"
Let>z=%z%+1
Goto>startreadline
Label>finishline

WriteLn>%TEMP_DIR%temp.txt,result,%output%
Let>k=%rec%/%rn%
Let>k=%k%*100
Let>k={round(%k%)}
Let>dlgProgress.progressbar=%k%
ResetDialogAction>dlgProgress


Until>rec=num_recs
ExecuteFile>%TEMP_DIR%temp.txt

Label>EOF

Exit>0

[Marcus Tettmar]

I found this:
http://blogs.technet.com/heyscriptinggu ... -name.aspx

So try:

Let>SQL=select * from [my file.csv]


Let>csvfile=my file.csv
Let>SQL=select * from [%csvfile%]

[kpassaur]

Thanks, it works great. I liked the comments on the site the link sent me to

After spending all that time and effort we were hoping the answer would be really complicated; that way we could say, “Well, it took us a long time, but that’s only because this is such a complex problem that it required incredible feats of intellectual reasoning and ability before we could determine the answer.â€

[jpuziano]

Thanks Marcus for finding this [solution] and kpassaur for posting the working script.

Added bonus. Ironically, after trying so hard to find one way to access text files that have spaces in their file names we ended up finding two ways. You can also enclose the file name using the ` character. This isn’t the single quote mark; instead it’s the accent grave character found on the same key as the ~ character (on U.S. English keyboards anyway).

Perhaps the ` character would also work for us in this script?

Great work guys and take care.

[kpassaur]

I just tested it with the accent grave characer and it works. This is most likely why I had so much difficulty as when it is on your screen it looks just like a single quote. I saw examples and always though it was a single quote so that is what I was using. Thank you