Access and Excel Imports - paid help wanted

[harlequin]

hi, wonder if anyone can give me a definative answer to our problem and we dont mind paying.

I do actually have a qualified SQL programmer starting in 6 weeks but i cannot wait any longer before going mad.....

i have posted about this before but i hope actinic may be able to answer this as it IS their software..;)

Ok the scenario is this..

sometimes we have clients who use excel sheets and some who use access databases for their products/stock etc...

Now then, we know that to specify subsections on a flat file import you should follow the format

Actinic_Sections (column header)
Section1 | SubSection2 | SubSection3

the above being in 1 column only and the pipe symbol, | , being what Actinic uses to make a subsection...(not a lot of people know that...)

However most people quite logically follow this format, and i copy from my clients database now...

First stage Second Stage Third Stage
Section1 SubSection2 SubSection3

where each of the above is a column all in its own right...

Because Actinic wants them all in one column, we therefore create a fourth column called Actinic_Sections and, heres where my ignorance comes in...

in excel we have used the concatenate function somthing like

=CONCATENATE(a:a,|,b:b,|c:c) please dont laugh at the syntax, i told you i was bad...anyway, when its done properly it runs to a fashion..

the problem comes when there isnt something in the Third Stage or Second Stage...the function above still puts in a | symbol i.e.

run query and if no second stage specified this is the result..

Actinic_Sections (column header)
Section1 ||

the extra pipe is there and knackers up the sections...

we need a query to essentially join the customers seperate columns into the one that actinic requires and yet remove the | pipe symbol where there is no more subsections (regardless of how many levels customers end up using...)

did i mention that a lot of customers use Access...we need the exact same query for that!

whilst you are at it, can someone also produce the two queries needed to simply change the decimal point and put the price into pennies, again another actinic 'quirk' and a constant pain in the butt...for us non-access/excel experts..., exporting to a text file was my only solution and doing find and replace all decimal points, but essentially a very unelegant solution and one used in desperation...

as i say i dont mind paying, but if anyone can post the solutions up here, i am sure it will help an awful lot of people who dont use the import facility to its full advantage....i alone had two people this week who gave up in the end trying to import the data because they couldnt make the subsections...thats the easy bit, but creating a standard query that can be modified to suit anyones particular collumn headings and automate the process has got to be the way forward...


Steve Quinn
Harlequin Domains
www.harlequindomains.com
0800 0832077
Actinic Design, Hosting and Marketing

[printerbase]

Hi,

If you got to excel help and search for If, you will learn about:
=IF(logical_test,value_if_true,value_if_false)

You should be able to use this to help your import
e.g.
If(a:a="","",a:a|)

which basically says:
if a:a empty then don't print anything
if a:a not empty then print a:a|

If you add this into you CONCATENATE formula for each section then it should work. I think.

I don't know about the exact format or contents for the If statement but hopefully it will give you something to go at, I am more of an Access person.

Peter

[harlequin]

how about a nice access query then ?? We really need both solutions to cover all bases...thanks anyway, glad you undestood me!

steve

[chrisbrown]

Hi Steve

The attached file contains an excel spreadsheet with a revised formula for the composite column. No need for CONCATENATE really. The following will do it:

=A1 & IF(LEN(B1)>0;"|" & B1; ) & IF(LEN(C1)>0;"|" & C1; )

The IF bit means:

If the LENgth of B1 is greater than zero then concatenate a pipe and B1 else concatenate an empty string.

[chrisbrown]

The file...

[harlequin]

and there was me going to go away from this lovely forum and post somewhere else in my desperation...thanks a million, will let yhou know how i get on with it....

any chance of the decimal point bit....

steve

[chrisbrown]

=E1*100

Where E1 is the cell containing the price including pennies.

[harlequin]

is there any good reason these functions or whatever they are actually called cannot be used in access??

steve

[printerbase]

In a query in access you could use the following as one of the output fields in the query assuming the data you where querying contained the fields: Section1 SubSection2 SubSection3

Section_Name: [Section1] & IIf(IsNull([SubSection2]),””, “|” & [SubSection2]) & IIf(IsNull([SubSection3]),””,”|” & [SubSection3])

Peter

[harlequin]

now im really smiling....although havent tried all this yet...]

steve

[Jan]

If access you need to use IIF in the query

so if you have


section1
section2
section3

you would say

select IFF(section2<> "", "", "|" + section2) AS S2, ", " + IFF(section3 <> "", "", "|" + section3) AS S3, select section1 + S2 + S3

Cheers,

[harlequin]

i am very very happy because the excel stuff is working brilliantly....

quick question, can an access output query write directly to the table the component parts come from....simple stuff i know..

will try access stuff after a strong coffee....

and i am learning something...thanks for the posts about what is actually forming the querys

i am opening new doors so thanks very much .... sorry for repeating myself, myself...

steve

ps, i think actinic should put the excel bit here into the user guide as lots and lots of people have their info in excel just never use the import facility because of the subsections issue and price in pennies thing...whereas they can simply have two extra colums with these functions in....and SIMPLY change the column numbers to suit wherever their own information is...nice one ...

[harlequin]

just played a little with the excel function above and found that nicely, for customer a who has 5000 products already i copied that forumla into the box and dragged the handles down to the bottom of his products and it copied the formula all the way down as it should..producing 5000 correctly priced products, in the correct sections...i am sooo happy...

the best bit, was when i added another product, say product 5001, filled in the sections and the price and excel automatically wrote the correct entries in the new colums...

this brought up another question and i would love more answers, how did excel know in the next row to use the formula automatically, does it use intelligent reasoning in that if you have done that for all your existing rows the chances are that you will want to do it for all others...

or.. was it a fluke..:D

and (do you like the new syntax of my questions, or, and....) is there the same thing in access, i.e. auto updating of columns as soon as the component parts are filled,

this i imagine is the best way because as i have imagined it so far, there will be a physical query button to press and then the query runs and populates the extra actinic columns....

far better then if we can remove the having to run a manual query and make the columns update themselves...

does that make sense...

[Jan]

There is no need to store the information in a separate column in access, just keep the original fields and then write a query to work out the rest. Then export the query to csv to create the import file. If you give the fields in the query the correct names (put name : in front of the field if you are using the access query builder or AS name after the field if you are using SQL.

If the data is used for external linking you will need to run a query to create a table before linking because Actinic cannot see linked tables or queries when looking for tables to link to but again you can create the query to select the fields in the format you want them and change the query into a make table query to easily create a table.

Regards