Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert Integer to String before Import to MS SQL with vbs

Posted on 2008-10-24
6
Medium Priority
?
708 Views
Last Modified: 2012-05-05
I have a csv file that gets generated nightly and I have a simple vbs script that imports the records from the csv into a table in MS SQL Server 2005 Express. This was working fine until my company started to use numbers for Salesman_ID the csv and vbs sees these files first and assumes that they are integers. I need to have these appear as a string value prior to import. I have the field set up in the SQL table as Text and have tried nchar(4), text and just about everything I can think of. I can get the import to function with the script below but the issue is the file exports with the integer based Salesman_ID export first and the Insert statement only brings them in all records come in but the string based Salesman_ID field is blank....I am stumped with this one. I have included the current code below...
Set conn = CreateObject("ADODB.Connection")
DBstring = "Provider=SQLNCLI;Server=.\SQLEXPRESS;Database=ComRep;Integrated Security=SSPI;Persist Security Info=True;" 
conn.Open DBstring	
 
sconnstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\gmcgifts\qcs_upload;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Set objConn = CreateObject("ADODB.Connection")
     objConn.Open sConnString
          set rs=objConn.execute("SELECT * FROM [DPU893R_test.csv]")
		  
	    rs.MoveFirst
               While Not rs.EOF
		   
if RS.Fields.Item(3).Value = "ms" then
 
	if IsNull(RS.Fields.Item(17).Value) or RS.Fields.Item(17).Value = "" then
	
	REC_NAME = "NO Name"
	
	else
	
	REC_NAME = RS.Fields.Item(17).Value
	
	end if
	
	if IsNull(RS.Fields.Item(20).Value) or RS.Fields.Item(20).Value = "" then
	
	PROD_DESC = "NO Name"
	
	else
	
	PROD_DESC = RS.Fields.Item(20).Value
	
	end if
	
		REC_NAME = REPLACE(REC_NAME, "'","")
		PROD_DESC = REPLACE(PROD_DESC, "'","")
			  
		
  
  theSQL = "INSERT INTO ReleaseOrder"
  theSQL = theSQL &"(SLSM_NO, MAIL_NAME, COMM_CODE, ORD_NO, EARN_ORD_PRICE, EARN_SLSM_COST, EARN_COMM_AMT, ORD_TYPE, CUST_NO, DATE_ENTER, CUST_INV_DATE, WIP_CODE, "
  theSQL = theSQL &"PROG_ID, RLS_ORD_NO, CUST_NAME, SEQ, ITEM_NO, ITEM_DESC, COMM_PRICE_FL, STATUS)"
  theSQL = theSQL &"Values('"&RS.Fields.Item(0).value&"', '"&RS.Fields.Item(1).Value&"', '"&RS.Fields.Item(3).Value&"', '"&RS.Fields.Item(5).Value&"', '"&RS.Fields.Item(6).Value&"', '"&RS.Fields.Item(7).Value&"',"
  thesql = thesql &" '"&RS.Fields.Item(8).Value&"', '"&RS.Fields.Item(9).Value&"', '"&RS.Fields.Item(10).Value&"', '"&RS.Fields.Item(11).Value&"',"
  theSQL = theSQL &" '"&RS.Fields.Item(12).Value&"', '"&RS.Fields.Item(13).Value&"', '"&RS.Fields.Item(15).Value&"', '"&RS.Fields.Item(16).Value&"', '"&REC_NAME&"', '"&RS.Fields.Item(18).Value&"',"
  theSQL = theSQL &" '"&RS.Fields.Item(19).Value&"', '"&PROD_DESC&"', '"&RS.Fields.Item(21).Value&"', '"&RS.Fields.Item(25).Value&"')"
 
  conn.execute(theSQL)
  Set theSQL = Nothing
  
  
  end if
  		
  
  
   
 
 
rs.MoveNext
   Wend
 
 
msgbox("Complete")

Open in new window

0
Comment
Question by:panhead802
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:CCongdon
ID: 22796135
Can we see a sample of your data in the CSV file?
By the way...VBScript does not 'see' integers or strings. All variables in VBScript are variants (or arrays). From MS's scripting site:
VBScript is a typeless language. This means that variables cannot be restricted to a single data type. VBScript does not allow you to specify in advance that a particular variable can hold only a particular kind of data. Instead, VBScript uses a single kind of variable, known as a variant, which can store any kind of data.
So, most likely the problem is in your table getting the Insert or in the data being saved to the CSV file.
0
 

Author Comment

by:panhead802
ID: 22796259
I had to convert the csv to a text file in order to upload it. I didn't think vbs saw strings or integers...I am grasping at straws now.
I also attached a smaller script I was using to test. It simply is supposed to display the salesman number in a message box, it works until it sees a string value...then it returns a null  value error. I am stumped. I thought at first it was an issue with my table but this script eliminated that.

Thanks in advance for any help
Dan
sconnstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\gmcgifts\qcs_upload;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Set objConn = CreateObject("ADODB.Connection")
     objConn.Open sConnString
          set rs=objConn.execute("select * FROM [DPU893R_test.csv]")'This opens the file uploaded
		  
		  
		  	   rs.MoveFirst'Move to the first record....
               While Not rs.EOF
			   
			   
			   
			   
			   
			   
			   msgbox(RS.Fields.Item(0).Value)
			   
			  
			   
			   rs.MoveNext
   			   Wend
 
 
 
msgbox("Deleted")

Open in new window

DPU893R-test2.txt
0
 
LVL 9

Expert Comment

by:CCongdon
ID: 22796402
I bet it's something in JET doing it to you. Since JET is database, it's probably having to assume it is one data type or another. And so it sets it to one data type...and then gets upset when it starts looking like another.
I modified your test script...see if it returns the SLSM_NO column for everything.

sconnstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\gmcgifts\qcs_upload;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Set objConn = CreateObject("ADODB.Connection")
objConn.Open sConnString
set rs=objConn.execute("select CSTR(SLSM_NO) FROM [DPU893R_test.csv]")'This opens the file uploaded
 
rs.MoveFirst'Move to the first record....
While Not rs.EOF
   msgbox(RS.Fields.Item(0).Value)
   rs.MoveNext
Wend
msgbox("Deleted")

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:panhead802
ID: 22796510
Tried the script exactly as posted. Null error went away but the msgbox returned a value for the integer type values in the csv and no value for the string type values.

It is very strange, if I remove the int type values from the csv all goes as planned if I move them to the end of the file all as planned.
0
 
LVL 9

Accepted Solution

by:
CCongdon earned 2000 total points
ID: 22796664
OK, that confirms it for me. The problem is in JET itself.
Edit your data file. Put double-quotes around all of the SLSM_NO data points. Run your script again.
The problem appears to be this....JET is a database engine. DB engines MUST have typed data. Text file, by nature, is typeless, so JET must make an implicit conversion when it creates the 'table' in memory. It sees numeric data in the field, so it creates the table with that column as a numeric data type...and then chokes somewhat when it gets to a string.
 
0
 

Author Comment

by:panhead802
ID: 22797097
It is definitely a jet issue, I have been looking into using a registry mod on the server.

Just tested and it worked like a charm:

changed
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\ImportMixedTypes from majority to text
and
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\MaxScanRows from 19 to 0

Seems to have fixed the Jet problem.
I am going to give you the points because you pointed me to the Jet issue. I don't think I would have caught that issue.
Thansks
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question