I have the following Excel code that I need to convert to SQL. I have looked (a little) at using cursors to do the looping, but I'm not sure how to define the cursors
-
Do While Worksheets("DATA").Cells(sCellRow, 1).Value <> ""
sPartNo = Worksheets("DATA").Cells(sCellRow, 1)
Do While Worksheets("DATA").Cells(sCellRow, 1) = sPartNo
sPartNo = Worksheets("DATA").Cells(sCellRow, 1)
'
' SO #
'
Select Case dCounter
Case 1 To 9
sNextSO = "E" & "0000" & dCounter
Case 10 To 99
sNextSO = "E" & "000" & dCounter
Case 100 To 999
sNextSO = "E" & "00" & dCounter
Case 1000 To 9999
sNextSO = "E" & dCounter
Case 10000 To 99999
sNextSO = dCounter
End Select
'
' PO Line Item
'
Select Case Worksheets("DATA").Cells(dCellRow, 25).Value
Case 1 To 9
sLineItem = "00" & Worksheets("DATA").Cells(dCellRow, 25).Value
Case 10 To 99
sLineItem = "0" & Worksheets("DATA").Cells(dCellRow, 25).Value
Case 100 To 999
sLineItem = Worksheets("DATA").Cells(dCellRow, 25).Value
End Select
Cells(dCellRow, 1).Value = sNextSO
'
' Release #
Cells(dCellRow, 2).Value = dReleaseNum
'
'
' Increment numbers
'
sCellRow = sCellRow + 1
dCellRow = dCellRow + 1
dReleaseNum = dReleaseNum + 1
Loop
dReleaseNum = 1
dCounter = dCounter + 1
Loop
-
Thanks
David Davis
Would you mind describing in words what your Excel loop is trying to do?
Would you mind showing some sample rows of your data, and describe how they are changed by the Excel loop?
Perhaps the task can be accomplished with some simple UPDATE statements in SQL Server. You may not need all the flexibility that cursors provide, at considerable performance expense.
If you are doing these tasks in Excel, it would seem almost guaranteed that your data table(s) are small, i.e., less than 65,000 rows.
Thanks.
Dan
|||Yeah, it looks like all you are doing is padding the data with zeros for display. If your data is all integers, it is best to store them as integers and simply format them for viewing in the client.
If you are doing a conversion, or you just have to do this for display, you can do something like:
select right('00000' + cast(1000 as varchar(5)), 5)
Returns
-
01000
Of course, I am just taking a guess at what you are actually trying to do.
|||DanR1
I have a spreadsheet that converts EDI Sales Order Delivery Schedule data, which is exported from a website(no control over exported format), The user copies the data from the different spreadsheets into 1 spreadsheet, then I copy data from the fields I need, then the user saves the spreadsheet as a csv file for import into SQL. I want to streamline this process.
The individual spreadsheets have the following format (I'm only listing the fields I use)
Field Data
PartNo 117933A1
PartRev D
Description PLATE, AXLE MTG.
DueDate 20070702
QtyDue 200
QtyType Firm
UOM EA
PlantCode BU
PONum N40136001
POLine 234
The spreadsheets have multiple line items (releases) for a given part #. I take the data (via code) and format to the following:
Field Data
SONO E00001 (Generated via code)
ReleaseNo 1 (1-n, Generated via code)
CustPartNo 117933A1 (original part #)
PartNo JI-117933A1 (our in house part #, Cenerqated via code)
PartRev D
Description PLATE, AXLE MTG.
DueDate 07/02/2007 (reformatted via code)
QtyDue 200
QtyType Firm
UOM EA
PlantCode BU
PONum N40136001-234 (combination of PONum and POLine, Generated via code)
Each part number needs to have a unique Sales Order #, each release needs to have a unique Release #. Using the above data as an example. when the spreadsheet is imported into SQL (which I have figured out how to do). You would see the following: (Example condensed)
The loops (I posted) control the generation of the SONO and ReleaseNo. I'm not sure how to generate them in SQL.
Thanks
David Davis
|||David,
The main method for auto-generating sequential numbers in SQL Server, at least the one with which I am most familiar, is to make a temporary table, e.g., #temp, (or maybe even a table-variable, @.temp), and declare one of its columns as INT IDENTITY (1,1) -- which indicates a SEED of 1 and an INCREMENT of 1.
I am not sure if the SEED or INCREMENT can be a variable, e.g., @.seed, or @.increment. (I don't know if variables can be used in TABLE definitions.)
I don't think you can do something like an alphabetic prefix. But once you have the numeric column, you can use an UPDATE statement to create such a column from the INT column.
With your example, you would then need something like
create table #temp
(
SONO varchar(20) null,
ReleaseNo int identity (1,1),
...
)
insert into #temp
(
CustPartNo,
PartNo,
...
)
select
CustPartNo,
PartNo,
...
from (some table from which you have imported the data from your data source)
update #temp
set SONO = 'E' + right(('0000' + cast(ReleaseNo as varchar)), 5)
from #temp
This last query will populate the SONO column based upon the auto-generated integers found in the ReleaseNo column.
You would then copy the data from #temp into whatever permanent storage table you are using. (You may have to tailor this example to your needs.)
Although I haven't tried it, I suspect you can have a "loop over spreadsheets" to concatenate all the spreadsheet data into a single table, the table that would be used to feed this process. I recall some SSIS forum posts that discuss "looping over input data files."
Does that help?
Dan
|||David,
As I look further at your original spreadsheet code, it appears that you may need two auto-generated numbers: one for SONo, and the other for RelNo.
From your examples I am unable to determine how you choose when a new SONo must be generated. I would be inclined to follow some sort of scheme relying on creating a temporary table, with a column that has auto-generated numbers. But in the temporary table for generating new SONo values, you would only place each "distinct" set of information that is supposed to be associated with a single SONo. (Does that make any sense to you?) A query of the data in your existing aggregate table should be able to tell you the next available number, which you would then use as the SEED for your INT IDENTITY (@.seed, @.increment) definition in your temporary table.
HTH.
Dan
|||DanR1
Thanks for the code for generating the SONo it's just what I was looking for. The only thing I have left to generate is the Release # (RelNo).
The RelNo field is simular to the SONo, with the exception that it has to be reset to 1 when the part # changes. I.E.
SONo RelNo SMIPartNo Qty
E00001 1 JI-87308664 100
E00001 2 JI-87308664 100
E00001 3 JI-87308664 100
E00001 4 JI-87308664 100
E00001 5 JI-87308664 100
E00002 1 JI-87337954 100
E00002 2 JI-87337954 100
E00002 3 JI-87337954 100
E00002 4 JI-87337954 100
E00002 5 JI-87337954 100
What options do I have for this?
Thanks
David Davis
|||David,
What I have done in similar circumstances is have another numeric column, not the one with the IDENTITY and SEED, and populate this other column from the auto-generated IDENTITY column, by subtracting the appropriate value.
To follow my earlier example, this would be something like
create table #temp
(
SONO varchar(20) null,
ReleaseNoTmp int identity (1,1), -- for auto-generating the numerical sequence
ReleaseNo int null, -- will eventually receive the desired values
...
)
insert into #temp
(
CustPartNo,
PartNo,
...
)
select
CustPartNo,
PartNo,
...
from (some table from which you have imported the data from your data source)
update t1
set ReleaseNo = t.ReleaseNoTmp + 1 - tmp.ReleaseNoBase
from #temp t1
inner join -- get the smallest ReleaseNoTmp for each SONo and SMIPartNo
(
select
SONo,
SMIPartNo,
min(ReleaseNoTmp) as ReleaseNoBase
from #tmp
group by
SONo,
SMIPartNo
) tmp
on t1.SONo = tmp.SONo and
t1.SMIPartNo = tmp.SMIPartNo
;
You will probably want some suitable "order by" statement in your INSERT statement, so that the entries for a SONo and SMIPartNo will appear in the desired sequence (and so that the ReleaseNo values will be in the desired sequence). If it is not possible to have a decent "order by" in that first INSERT statement, you might need a second processing step, in which you create a new temporary table, with a new set of RelaseNoTmp values that will be in the desired order.
Will that work for you?
Dan
No comments:
Post a Comment