Macro/VBA Help - splitting and adding rows (or Access instead?)

[RegistrationMom]

So I'm collecting web form entries in csv file.

Current row headings - Parent, NoOfChildren, Child1, Child1Details, Child2, Child2Details, Child3, Child3Details

I would like to get a more 'vertical' listing, ie -

Parent1, Child1, Child1Details
Parent1, Child2, Child2Details
Parent1, Child3, Child3Details
Parent2, Child1, Child1Details
Parent2, Child2, Child2Details
Parent3, Child1, Child1Details

I also putz a bit in Access but am unable to contrive a query that does this - so I figure if I can get the Excel sheet list in line, I'm good to go. . . I'll cross-post this in the Excel Forum.

This is bit more complicated than just 'transpose!'

Using MS Office 2007

Thanks so much!

 

[Micron]

Don't know how your data is arranged in Access table that you've been 'putzin' with, but I'm thinking a cross-tab query could be a solution. But not if a csv file is your datasource. If that's the case, you will need a custom function that uses the split/join functions in Access. You can retrieve results in a query if you add your function call for each value that you want to retrieve, passing as the argument. Obviously, this approach is not very dynamic if you want to make changes to the size of your array or change field names afterwards.

 

[Joe4]

Cross-posted: http://www.mrexcel.com/forum/excel-...elp-splitting-adding-rows-access-instead.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[RegistrationMom]

Sorry!

Here's the link to the Excel forum post - same title.
http://www.mrexcel.com/forum/excel-...elp-splitting-adding-rows-access-instead.html

 

[RegistrationMom]

Thanks Micron.

I don't really have anything set-up in Access. I download my csv file - open in Excel - the import to Access, in hopes of being able to set-up an simple query. I'll check a crosstab - but you lost me with passing arguments and my array size!

Appreciate your time!

 

[Micron]

if your array (cs values) is something like (1,2,3,4,5) the split function will return the nth value if you tell it to get n. You create a custom function something like
function getAryValue
'in here you define the variables, get the array string via recordset for example
'and use the split function to get the nth value, based on what the separator is (,)
'and make getAryValue = 'the nth character' disregarding ,'s
end function
In a query design view, you put MyFieldName:getAryValue(1) in a column, then MyFieldName2:getAryValue(2) in the next column and so on.

you cannot separate the csv values with an ordinary query. Your other option is to use Excel's text to columns feature to split the values into columns and use that either in Excel or Access.