Solved

Exce help needed - How do I  convert to a database loadable csv file

Posted on 2013-01-18
10
142 Views
Last Modified: 2013-02-27
I believe there must be an easier way to format the data in an excel spreadsheet
Can someone please help me out here???
Here is the problem.
The data looks as follows

StoreId,product,        price  StoreId,product,        price
1           ,Castle Toy,    30.00  2           PinePen        12.50
1            SeaPlane,     45.00   2           GumDrop     5.77
1            HoolaHup6, 12.67   2           UniveralFan 26.99   ...

The data is broken out across the columns by storeID e.g 3,4,5,6... N
Right now I copy and paste each 2 columns in an effort to create a 2 field list for loading
How would I do this in a more efficient manner in  Excel???
StoreId,product,        price
1           ,Castle Toy,    30.00
1            SeaPlane,     45.00  
1            HoolaHup6, 12.67
2           PinePen        12.50
2           GumDrop     5.77
2           UniveralFan 26.99


I imagine for the skilled Excel person this is simple - Please advise!!
0
Comment
Question by:Robert Silver
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38795121
are the column names in excel exactly like this

StoreId,product,        price  StoreId,product,        price
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38795164
A sample file would be helpful...
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38795175
if the columns is what you posted,
change column names like this

StoreId,product,        price  StoreId1 ,product1,        price1

then, from access db, create a link to the excel file..

you can now combine the columns with a union query


select StoreId,product, price from tableExcel
union all

select StoreId1,product1, price1 from tableExcel
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38795369
Hi, rssdds.

I couldn't decode how the input data was organised, so in the attached I've taken a chance and assumed that there shouldn't be a space in ",Castle Toy,". (Although, now that I look at it again, is it possible that a comma before a string is a text qualifier?)

Please change the text file's path and name as necessary.
Option Explicit

Sub Split_Stores()
Dim i          As Long
Dim j          As Long
Dim xLast_Col  As Long
Dim xLast_Row  As Long
Dim xLast_Dest As Long
Dim xLast_Item As Long
Dim xSrce      As Worksheet
Dim xDest      As Worksheet

Application.ScreenUpdating = False
    
    Workbooks.OpenText Filename:="D:\Store_Details.txt", _
            Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
            Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1)), _
            TrailingMinusNumbers:=True
    Set xSrce = ActiveWorkbook.Sheets(1)
    
    If xSrce.UsedRange.Rows.Count < 1 Then Debug.Print "!?"
    xLast_Row = xSrce.Range("A1").SpecialCells(xlLastCell).Row
    xLast_Col = xSrce.Range("A1").SpecialCells(xlLastCell).Column
    
    If xLast_Row < 2 Or xLast_Col Mod 3 <> 0 Then
        xSrce.Activate
        MsgBox ("Invalid rows (" & xLast_Row & ") or columns (" & xLast_Col & ") - run cancelled.")
        Exit Sub
    End If
    
    Set xDest = ThisWorkbook.Worksheets.Add
    xDest.Range("A1:C1") = Array("Store ID", "Product", "Price")
    
    For i = 1 To (xLast_Col - 2) Step 3
        xLast_Item = xSrce.Cells(Rows.Count, i).End(xlUp).Row
        If xLast_Item > 1 Then
            xLast_Dest = xDest.Range("A1").SpecialCells(xlLastCell).Row
            xSrce.Range(xSrce.Cells(2, i), xSrce.Cells(xLast_Item, i + 2)).Copy Destination:=xDest.Range("A" & xLast_Dest + 1)
        Else
            MsgBox ("No data from column no. " & i & " - missing store bypassed.")
        End If
    Next
    
    xDest.Columns("B:B").Replace What:=",", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    xSrce.Parent.Close savechanges:=False
    
    xDest.Activate
    
Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
Split-Stores-V2.xlsm
Store-Details.txt
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 38795968
I can program - I am looking for a quick Excel is solution like maybe a menu  method or such...  It seems to me there should be a way to facilitate it

I will get back to you in a week
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 26

Expert Comment

by:redmondb
ID: 38796369
rssdds,

Pasting data directly into a question can cause layout problems, so please post a larger sample as a text file. This may give us a clearer view of the file's format.

Also, do you want the commas in the output list?

Brian.
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 38935755
I finally got to this.  Brian what References does this code require on an Access 2010
to run or do I need to run it in Excel? If so how? I am only aware of running it in Access.
Not a lot of error messages display to tell me which libraries I need

As soon as I run this in Access 2010 successfully you get A+ points but now I struggle to know what references the subroutine needs. I even tried to change it into a function that returns a variant and running in in the immediate window: ? split_Stores()
So far I have :
     Visual Basic For Applications
     Microsoft Access 14.0 Object library
     OLE Automation
     Microsoft Office 14.0 Access database engine Object Library
   
The compile shows Worksheet is a user defined type not defined after compiling....
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38936032
rssdds,

As soon as I run this in Access 2010 successfully...
You'd repeatedly mentioned Excel assistance in an Excel Topic so I provided an Excel solution. At this point, let's continue with that and check that the logic's correct. Once that's done, we can convert it to Access.

...you get A+ points
Only "A" and only when you've got the solution you need.

do I need to run it in Excel? If so how?
(1) Open Split-Stores-V2.xlsm.
(2) Edit its Split_Stores() macro to reflect your file's name and path.
(3) Click the blue button on Sheet1.

Brian.
0
 
LVL 2

Author Closing Comment

by:Robert Silver
ID: 38936177
Note this module must be tailored a bit but it seems to work what it does is create a worksheet output as a list of data from the horizontal positions of repeating data down and across

No need to Access -ize it but note Microsoft does not make this easy at all especially if you worked on versions of Office 2003 and backwards.
This must be loaded into a module and run within Excel!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38936248
rssdds,

This must be loaded into a module and run within Excel!
Nope - it's already there!

what it does is create a worksheet output as a list of data from the horizontal positions of repeating data down and across
Other than some uncertainty around the delimiter, it seems to produce exactly the sample output in your question

Note this module must be tailored a bit
Other than the text file's path and name that I mentioned, is there anything else that needs changing?

Brian.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now