[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Populate a SQL table from  a array

Posted on 2010-04-07
6
Medium Priority
?
451 Views
Last Modified: 2013-11-10
Hello i am extracting some range from a excel sheet. i am working in VB
worksheet.Range("clientes").Cells.Value(excel.XlRangeValueDataType.xlRangeValueDefault)
It return me a array objet How can i insert this array into a SQl table
Thanks
0
Comment
Question by:Saul_l
  • 3
  • 2
6 Comments
 
LVL 9

Accepted Solution

by:
blandyuk earned 1000 total points
ID: 30064581
Simply execute an INSERT statement and bind the array data values to it:

INSERT INTO [table_name] (field1, field2, field3, etc)
VALUES (" + array(0) + ", " + array(1) + ", " + array(2) + ...)
0
 

Author Comment

by:Saul_l
ID: 30071939
thanks, but the problem is that the array is multidimensional (1,1) (1,2) (2,1).........
and the other problem is that i dont know how many columns i have.
thanks
0
 
LVL 36

Assisted Solution

by:Miguel Oz
Miguel Oz earned 1000 total points
ID: 30080738
Result arrays are one based. (object[,])
Dim Values(,) As Object = worksheet.Range("clientes").Cells.Value(excel.XlRangeValueDataType.xlRangeValueDefault)

To get the number of columns
Dim colMax As Integer = Values.GetUpperBound(1)

Then you can rewrite the insert statement as:
INSERT INTO [table_name] (field1, field2, field3, etc)
VALUES (" + Values(1,1) + ", " + Values(1,2) + ", " + Values(1,3) + ... +Values(1,colMax))

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:Saul_l
ID: 30084168
thanks a lot.
I am tring also to get the range values as XML this is my instruction
Dim valores = Worksheet.Range("clientes[#todo]").Value(excel.XlRangeValueDataType.xlRangeValueXMLSpreadsheet)

and then this XML i am trying to insert it in SQL table using this code
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML
 INSERT INTO Clientes SELECT * FROM OPENXML(@XMLDocPointer,'/Table',2)

i get an error that the definition not correspond to the table any idea?

thanks
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <Borders/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Hoja1">
  <Names>
   <NamedRange ss:Name="DatosExternos_1" ss:RefersTo="=Hoja1!R1C1:R15C13"/>
  </Names>
  <Table ss:ExpandedColumnCount="13" ss:ExpandedRowCount="15"
   ss:DefaultColumnWidth="60" ss:DefaultRowHeight="15">
   <Column ss:Width="52.5"/>
   <Column ss:Width="45"/>
   <Column ss:Width="51"/>
   <Column ss:Width="54.75"/>
   <Column ss:Width="48.75"/>
   <Column ss:Width="52.5" ss:Span="1"/>
   <Column ss:Index="8" ss:Width="38.25"/>
   <Column ss:Width="36.75"/>
   <Column ss:Width="48"/>
   <Column ss:Width="32.25"/>
   <Column ss:Width="27.75"/>
   <Row>
    <Cell><Data ss:Type="String">NoCliente</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Nombre</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">ApellidoP</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">ApellidoM</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Direccion</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Telefono1</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Telefono2</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Celular</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Correo</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Cobrador</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Titulo</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Zona</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Comentario</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s62"><Data ss:Type="Number">1</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Saul</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Laniado</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Roman</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">nuevo</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Sr.</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">2</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">saul</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">romano</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">nuevo</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Sr.</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">3</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Nissim</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Memun</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">sa@</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Sr.</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">4</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Shlomi</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">abadie</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">6</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">salo</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">laniado</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">7</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">salomon</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">laniado</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">8</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">saul1</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">laniado1</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:Index="9"><Data ss:Type="String">sa@</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">9</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">saul2</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">laniado2</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">10</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">david</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Laniado</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Romano</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">11</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">David</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Cihen</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Cherem</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">12</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Jonny</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">perez</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">preer</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">13</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Moises</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Cohen</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">alfie</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">14</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Dan</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Cohen</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Levy</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">15</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">saul8</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">laniado2</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

Open in new window

0
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 30150525
Glad it helps to solve your original question,
Regarding your next question please post your complete error message.
What is "Clientes" sql definition?
0
 

Author Comment

by:Saul_l
ID: 30163543
I think yhe firs Question was solved and the second Question posted a other question to not mix the questions
thanks
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

607 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