Prev: Ctrl+E to center
Next: about screenupdating
From: Sammy on 25 Mar 2010 01:50
I have data in Excel that is in 2 columns as below

Coloum 1 Coloum2
000199 CAON00000013
000200 CAON00000011


I wanted to create my SQL script from these columns as below

Update apdoc set user1 = 'Coloum2 value' where refnbr = 'Colum1 value'

As I have thousands of lines I have tried concatenate etc but it does not
come out ok as the ' don't come out ok and then leading 000 disappear in the
concatenate.

I would really appreciate some help on this.

Thanks

Sam




From: Dave O on 25 Mar 2010 07:40
Is this an Excel question or an SQL question? Your post says you have
the data in Excel and that SQL is dropping the lead apostrophe and
leading zeros. I know nothing about SQL, but can you declare the
variable in SQL as text so it will read those values as text?

As a workaround, if you write a VBA script that writes the data out of
Excel and into a text file, can you import the text file into SQL? The
VBA would look like

sub WriteToTextFile()
dim OutputString as string

open "c:\abc.txt" for output as #1 'write to a file called abc.txt on
your C: drive

range("a1").select 'change this cell reference to the upper left
corner of your data

do until activecell.value = "" 'perform loop functions until encounter
a blank row
outputstring = activecell.value & activecell.offset(0,1).value
print #1, outputstring
outputstring = ""
activecell.offset(1,0).select
loop

close #1
end sub

Dave O
Eschew Obfuscation
 | 
Pages: 1
Prev: Ctrl+E to center
Next: about screenupdating